CODEWITHSUNDEEP
sql
S.E Saint
S.E Saint

Reputation: 135

SQL join on multiple columns in same tables?

I have a data table and a code table, And I want to join one column of the code table to two columns in the data table.

I didn't find a way to join so I tried:

SELECT
A.COLUMN1,
    (SELECT CODE_NAME FROM CODE_TABLE B WHERE B.CODE = A.COLUMN2) AS COLUMN2,
    (SELECT CODE_NAME FROM CODE_TABLE B WHERE B.CODE = A.COLUMN3) AS COLUMN3
FROM DATA_TABLE A ;

DATA_TABLE
COLUMN1 COLUMN2 COLUMN3
test     AA001    BB001
test1    AA002    BB002
test2    AA003    BB003

CODE_TABLE
CODE    CODE_NAME
AA001     APPLE
AA002     SAMSUNG
AA003     OPPO
BB001     LG
BB002     HWAWEI
BB003     GOOGLE

How to treat it as a join rather than a subquery?

Upvotes: 0

Views: 97

Answers (2)

Nick
Nick

Reputation: 147166

You can implement it as a JOIN with two independent JOINs to CODE_TABLE, one for each value in DATA_TABLE:

SELECT A.COLUMN1,
       B1.CODE_NAME AS COLUMN2,
       B2.CODE_NAME AS COLUMN3
FROM DATA_TABLE A
JOIN CODE_TABLE B1 ON B1.CODE = A.COLUMN2
JOIN CODE_TABLE B2 ON B2.CODE = A.COLUMN3

If it's possible that a value from COLUMN2 or COLUMN3 in DATA_TABLE might not exist in CODE_TABLE, you should use LEFT JOIN instead, and COALESCE on the output so as not to return a NULL value:

SELECT A.COLUMN1,
       COALESCE(B1.CODE_NAME, '') AS COLUMN2,
       COALESCE(B2.CODE_NAME, '') AS COLUMN3
FROM DATA_TABLE A
LEFT JOIN CODE_TABLE B1 ON B1.CODE = A.COLUMN2
LEFT JOIN CODE_TABLE B2 ON B2.CODE = A.COLUMN3

Output (for your sample data)

COLUMN1 COLUMN2 COLUMN3
test    APPLE   LG
test1   SAMSUNG HWAWEI
test2   OPPO    GOOGLE

Demo on dbfiddle

Upvotes: 2

SchmitzIT
SchmitzIT

Reputation: 9552

SELECT
    A.COLUMN1,
    B.CODE_NAME AS COLUMN2,
    C.CODE_NAME AS COLUMN3
FROM DATA_TABLE AS A
    INNER JOIN CODE_TABLE AS B ON B.CODE = A.COLUMN2
    INNER JOIN CODE_TABLE AS C ON C.CODE = A.COLUMN3
 ;

Note: Try and provide proper aliases, rather than A, B, c, in order to increase readability.

Upvotes: 1

Related Questions