CODEWITHSUNDEEP
mysql
spiderplant0
spiderplant0

Reputation: 3952

Return items present in one MySQL table that are not present in the other with 2 columns to consider

I want to return all the postcodes in table1 that are active and that dont have any items in table2 that share the same coordinates (lat,lng). I.e. in the below return :

AB11AC

I know there are several method where you are just checking one column, but not sure how to adapt for 2 columns. Should I just concatenate the 2 columns together in the query or is there a more efficient method? My tables each have around 2 million entries.

table1:

postcode  lat  lng active
-------------------------
AB11AA   55   1    Y
AB11AB   56   1    Y
AB11AC   57   1    Y

table2:

postcode  lat  lng active
--------------------------
AB11AA   55   1   Y
AB11AD   56   1   Y
AB11AE   59   1   Y

Upvotes: 0

Views: 31

Answers (1)

Taryn
Taryn

Reputation: 247680

You can use a LEFT JOIN:

select *
from table1 t1
left join table2 t2
  on t1.lat = t2.lat
  and t1.lng = t2.lng
where t1.active = 'Y'
  and t2.postcode is null

See SQL Fiddle with Demo

Or you can use a NOT EXISTS in the WHERE clause:

select *
from table1 t1
where t1.active = 'Y'
  and not exists (select *
                  from table2 t2
                  where t1.lat = t2.lat
                    and t1.lng = t2.lng)

See SQL Fiddle with Demo

Upvotes: 1

Related Questions