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?
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);