SQL combining tables with conditional formatting

Question

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

Tim Biegeleisen · Accepted Answer · 2021-07-27 03:12:24Z

1

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.

answered Jul 27, 2021 at 3:12

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL combining tables with conditional formatting

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related