CODEWITHSUNDEEP
sqlamazon-redshift
mayur392
mayur392

Reputation: 3

Case when column a from tableX is present in column b of tableY

Below is my use case, i'm querying redshift tables, using case when but get error in case when statement. ERROR: Statement 2 is not valid. ERROR: syntax error at or near "b"

MY SQL query:

CREATE TEMP TABLE TABLE1 AS
(SELECT
COL1
,COL2
,COL3
FROM XYZ_TABLE
WHERE CONDITION1
AND CONDITION2);

CREATE TEMP TABLE TABLE2 AS
(SELECT DISTINCT
COL1
FROM ABC_TABLE
WHERE CONDITION1
AND CONDITION2);


SELECT
COL1
,COL2
,COL3
,CASE WHEN (a.COL1 IN b.COL1) THEN 1 ELSE 0 END AS IN_TABLE_B
FROM TABLE1 a
LEFT JOIN TABLE2 b
WHERE a.COL1 = b.COL1

What i want to achieve:

TABLE1
-----------------
ID   |  NAME   | COL1
-----------------------
123  |   A    | BLA
234  |   B    | BLAA
345  |   C    | BLAH
456  |   X    | XXX
567  |   N    | FLS


TABLE2
-----------------
ID   |  COL1   | COL2
-----------------------
123  |   SKLJF    | BLA
345  |   DKLUF    | BLAH
567  |   KKBDL    | FLS

DESIRED OUTPUT ( IS ID IN TABLE1 PRESENT IN TABLE2, IF YES THEN 1 ELSE 0 END AS COLUMN_NAME)

----------------------------------------------
ID   |  IN TABLE B   |
----------------------------------------------
123  |   1    | 
234  |   0    | 
345  |   1    | 
456  |   0    | 
567  |   1    |

Upvotes: 0

Views: 273

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

I might use exists logic here:

SELECT
    t1.ID,
    CASE WHEN EXISTS (SELECT 1 FROM TABLE2 t2 WHERE t1.COL1 = t2.COL1)
         THEN 1 ELSE 0 END AS IN_TABLE_B
FROM TABLE1 t1
ORDER BY t1.ID;

This approach is robust to a record from the first table having multiple matches.

Upvotes: 1

Caius Jard
Caius Jard

Reputation: 74605

You wouldn't use "IN" for this even though your desire mentions the word "in"

You're performing a left join, which will have a value for b.ID where the join succeeded and a null where it failed, hence:

SELECT
a.ID
,CASE WHEN b.ID IS NULL THEN 0 ELSE 1 END AS IN_TABLE_B
FROM TABLE1 a
LEFT JOIN TABLE2 b
WHERE a.ID = b.ID

Upvotes: 1

Related Questions