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?

Gordon Linoff · Accepted Answer · 2015-09-30T11:35:21.790

7

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

edited Sep 30 '15 at 11:35

answered Sep 30 '15 at 11:21

Gordon Linoff

1,242,037
58
646
786

jpw · Answer 2 · 2015-09-30T11:30:43.437

1

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

edited Sep 30 '15 at 11:30

answered Sep 30 '15 at 11:24

jpw

44,361
6
66
86

You would think that wouldn't you. But my table1 is about 10,000 lines long and the above query returns nearly a million rows.. – Johnathan Sep 30 '15 at 11:50
@Johnathan If it returns a million rows the only plausible explanation is that you have a million rows matching the conditions in the second table. You could change the select to: `select distinct t1. location, t1.warehouse` to eliminate duplicates, but I would think that the answer by Gordon Linoff might be more appropriate in your case. – jpw Sep 30 '15 at 11:58
1

The second table only has 3500 rows so it is strange. I have marked the answer given by @Gordon below as it is getting the results I need. – Johnathan Sep 30 '15 at 12:03

StoYan · Answer 3 · 2015-09-30T11:27:46.573

0

You need to use JOIN. I'll design the query in a while :)

EDIT:

SELECT
  1.location
, 1.warehouse
FROM table1 1
JOIN table2 2 ON 1.location = 2.area AND 1.warehouse = 2.code

edited Sep 30 '15 at 11:27

answered Sep 30 '15 at 11:21

StoYan

255
2
10

This was my initial query, but my table1 is about 10,000 lines long and the above query returns nearly a million rows and I can't get why. – Johnathan Sep 30 '15 at 11:51
Maybe because you have duplicating rows in one or both of the tables and that leads to Cartesian product. – StoYan Sep 30 '15 at 13:59

Where clause to check against two columns in another table

3 Answers3