MS SQL - Two tables, simple flag calculation to check if a value is present in another table

Question

Long time viewer and my first question. Please be gentle. I am having issues writing a query that incorporates tables with 1-1 / 1-M relationships. To keep it simple - I have two tables

Tables

Query - Provide the entire list of cases from Table 1 and add a new column that has a flag (Y/N) if case has a car from table 2 whilst keeping the 1-1 relationship

Outputs

Answer 1

Try using exists logic to check, for each table 1 record, if it has a matching car record in the second table:

SELECT
    t1.caseno,
    CASE WHEN EXISTS (SELECT 1 FROM Table2 t2
                      WHERE t1.caseno = t2.caseno AND t2.Product = 'Car')
         THEN 'Y' ELSE 'N' END AS car_flag
FROM Table1 t1
ORDER BY
    t1.caseno;

Demo