How to select data and conditionally combine it in one query-CodePudding

I have the following tables:

V | H
-- --
a   q
s   w
d   e
f   r

VH
--
 a
 w
 d
 e

And I need the following result:

V | H | RES
-- --- ----
a   q    V
s   w    H
d   e    B

I.e., I need to select that rows from the T1 which V or H (or both) values exist in the T2 and additional column RES which value should be 'V' if the T1.V value is found in the T2, 'H' if the T1.H value is found in the T2, or 'B' if both of the T1.V and T1.H values are present in the T2 table.

I need the ANSI SQL query, without any specific DB-engine syntax.

CodePudding user response：

You can use CASE WHEN to achieve that

SELECT T1.*, 
CASE WHEN T1.V IN (SELECT VH FROM T2) AND T1.H IN (SELECT VH FROM T2) THEN 'B'
WHEN T1.V IN (SELECT VH FROM T2) THEN 'V'
WHEN T1.H IN (SELECT VH FROM T2) THEN 'H' ELSE 'N' END AS RES
FROM T1
WHERE RES <> 'N'

CodePudding user response：

I found one more way:

SELECT 'V' AS RES, T1.* FROM T1
WHERE T1.V IN (SELECT VH FROM T2) AND T1.H NOT IN (SELECT VH FROM T2)
UNION SELECT 'H', T1.* FROM T1
WHERE T1.V NOT IN (SELECT VH FROM T2) AND T1.H IN (SELECT VH FROM T2)
UNION SELECT 'B', T1.* FROM T1
WHERE T1.V IN (SELECT VH FROM T2) AND T1.H IN (SELECT VH FROM T2)