Can this be done with a single SQL Join?

Question

I am not sure if this can be done with a single JOIN, but I basically have two tables with an ID column in common. To make it simple I'll say Table A just contains an ID while Table B contains an ID and Code. There is a 1:M relationship between Table A and Table B, however it's also possible an ID from Table A is not contained in Table B at all. I was hoping to have a query return every ID that exists in Table B within a particular code range, or does not exist in Table B at all.

I tried using a LEFT JOIN with something like:

SELECT A.id FROM A LEFT JOIN B ON A.id = B.id AND b.code BETWEEN '000' AND '123'

But, this still gives me the IDs that exist in Table B outside of the code range.

Are you using mysql or sql server? They are not the same thing. — John Conde, Jun 13 '14 at 19:50
Pretty sure `Between` doesnt work like that. `Between 0 and 123` makes some sense. Add `and b.code is null` to get those that dont exist in `b`. — crthompson, Jun 13 '14 at 19:53
You should specify which `id` you want on the select `a.id` I assume. — Jess, Jun 13 '14 at 19:54

Guffa · Accepted Answer · 2014-06-13T20:24:06.500

3

Use a left join, and filter the result to contain the codes in the range, and also the lines where there is no matching record in table B:

select
  A.id
from
  A
  left join B on B.id = A.id
where
  B.code between '000' and '123' or B.id is null

edited Jun 13 '14 at 20:24

answered Jun 13 '14 at 19:54

Guffa

687,336
108
737
1,005

score 0 · Answer 2 · answered Jun 13 '14 at 19:58

0

What about

SELECT id FROM A LEFT JOIN B ON A.id = B.id 
WHERE b.code IS NULL OR b.code BETWEEN ' ' AND '123'

answered Jun 13 '14 at 19:58

ElMent

158
2
11

Can this be done with a single SQL Join?

2 Answers2