CODEWITHSUNDEEP
postgresql
Socram
Socram

Reputation: 25

SQL combining tables with conditional formatting

If I have an index table

Index
ID  Last    First
01  Smith   Frank
02  Biden   Joe
03  Trump   Don

and another table showing those who meet a certain measure

Measure
ID  Last    First
02  Biden   Joe
04  Obama   Barack

how can I query this in postgresql to show a listing of those on the index table and their status of meeting the measure?

Desired Output
ID  Last    First  Measure
01  Smith   Frank  No
02  Biden   Joe    Yes
03  Trump   Don    No

Upvotes: 1

Views: 190

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

You could use a left anti-join approach:

SELECT
    i.ID,
    i.Last,
    i.First,
    CASE WHEN m.ID IS NOT NULL THEN 'Yes' ELSE 'No' END AS Measure
FROM "Index" i
LEFT JOIN Measure m
    ON m.Last = i.Last AND m.First = i.First
ORDER BY i.ID;

Note that you should, in general, avoid naming your tables and other database objects using reserved SQL keywords like INDEX. I have placed your INDEX table into double quotes above for this reason.

Upvotes: 1

Related Questions