Where clause to check against two columns in another table

Question

I am struggling to get this answer for some reason.

I have two tables, table1 and table2 which look like this:

Table1:

ID   Location  Warehouse
1    London    Narnia
2    Cyprus    Metro
3    Norway    Neck
4    Paris     Triumph

Table2:

ID   Area      Code
1    London    Narnia
2    Cyprus    Metro
3    Norway    Triumph
4    Paris     Neck

I need to first select everything from table1 where table1.Location is in table2.Area AND table1.Warehouse is in table2.Code GIVEN THAT table1.Location is in table2.Area. I.e. I want:

ID   Location  Warehouse
1    London    Narnia
2    Cyprus    Metro

I have got to:

select
  1.location
, 1.warehouse
from table1 1
where 1.location in (select area from table2)
and 1.warehouse in (select code from table2)

But this won't work because I need the second where clause to be executed based on the first where clause holding true.

I have also tried similar queries with joins to no avail.

Is there a simple way to do this?

Solution 1

Use exists:

select t.location, t.warehouse
from table1 t
where exists (select 1
              from table2 t2
              where t.location = t2.area and t.warehouse = t2.code
             );

I should point out that some databases support row constructors with in. That allows you to do:

select t.location, t.warehouse
from table1 t
where(t1.location, t1.warehouse) in (select t2.area, t2.code from table2 t2);

Solution 2

Maybe I'm missing something, but a simple join on the two conditions would give you the result in your example:

select t1.*
from table1 t1
join table2 t2 on t1.Location  = t2.Area 
              and t1.Warehouse = t2.Code;

Result:

| ID | Location | Warehouse |
|----|----------|-----------|
|  1 |   London |    Narnia |
|  2 |   Cyprus |     Metro |

Sample SQL Fiddle