Struggling with CASE SQL Query

September 8, 2022

I’ve got 2 pretty simple SQL tables. Each consist of an id, name, x and y value. table "a" has 6 entries and table "b" 8. I am trying to make an SQL query that returns all the names of table "a" and a case column called status that has a value of "y" if the x and y values in table "a" are the same of that in table "b". and "n" if they are not. However, this returns a total of 48 results (6 x 8). Where as the expected result is just 6. How can I fix this?

select
    a._name,
    CASE WHEN (a.x_coord = b.x_coord and a.y_coord = b.y_coord) THEN 'y' ELSE 'n' END as status

from
    table_a a, table_b b

>Solution :

Try this

   select
        a._name,
        CASE WHEN (a.x_coord = b.x_coord and a.y_coord = b.y_coord) THEN 'y' ELSE 'n' END as status
    
    from
        table_a a
    left join table_b b
    on a.id = b.id

This query will return only 6 records from Table A if Table B has all the id’s that are available in Table A