CODEWITHSUNDEEP
sqlsql-server
ProdSupport
ProdSupport

Reputation: 81

How to cross reference an id to another column in sql?

TableA

Given the table shown above, what I need to do is if the COUNTRY field for a particular ID is NULL, then pick that corresponding UNDERLYING_ID, and cross-reference it to the ID column and pick the COUNTRY value from the row where the UNDERLYING_ID matches the ID.

So in this case, A701 will have a country value of JAP because the underlying_ID A706 has a match in the ID column (last record)

Not sure how to write the query here. Please advise.

Upvotes: 1

Views: 2194

Answers (2)

TZHX
TZHX

Reputation: 5377

Based on your statement that there doesn't need to be multiple levels of look-ups for a "NULL" country value, I think a sub-select inside a CASE statement fits this pattern of data well, and have used it in similar circumstances.

Something like:

SELECT
    CASE WHEN COUNTRY IS NOT NULL THEN COUNTRY
       ELSE ( SELECT T2.COUNTRY FROM [TABLE NAME] T2 WHERE T2.ID = T.UNDERLYING_ID )
       END AS Country
FROM [TABLE NAME] T

The engine will very likely convert this to a left join so it performs the same as @Cetin's answer, but in my opinion this is a more readable solution.

If you want to replace the NULL value with a specific value, you can use COALESCE for this, like:

SELECT
    COALESCE(
        CASE WHEN COUNTRY IS NOT NULL THEN COUNTRY
        ELSE ( SELECT T2.COUNTRY FROM [TABLE NAME] T2 WHERE T2.ID = T.UNDERLYING_ID )
        END, 
        '(ReplacementValue)') AS Country
FROM [TABLE NAME] T

Upvotes: 1

Cetin Basoz
Cetin Basoz

Reputation: 23797

select t1.ID, coalesce(t1.country, t2.country) as country
from myTable t1
left join myTable t2 on t1.Id = t2.underlying_id

Upvotes: 1

Related Questions