Need help with an SQL Query

Question

Hi all,

I currently have two tables that look something like this:

Table 1

Id | AddressNumber | AddressStreet | AddressZip
------------------------------------------------
1   | 50           | Fake          | 60101
2   | 300          | Fake          | 60101
3   | 50           | Fake2         | 60101
4   | 50           | Fake          | 60103

Table 2

AddressLowRange | AddressHighRange | AddressStreet | AddressZip
---------------------------------------------------------------
50             | 200              | Fake          | 60101
20             | 50               | Other Fake    | 70102

i need to find a list of Ids from table 1 where the address is not in Table 2. So for the sample data above i'd get back ids 2, 3 & 4.

Thanks

Answer 1

does the address is not in Table 2 means addressStreet

if so:

select id from table1 where addressStreet not in (select distinct addressstreet from table2)

and

select * from table1 where addressStreet not in (select distinct addressstreet from table2)

for all fields

Answer 2

select id from "Table 1"
where AddressStreet not in (select distinct AddressStreet from "Table 2")

Answer 3

Try this.

SELECT Id
FROM Table1
WHERE NOT EXISTS
    (SELECT *
    FROM Table2
    WHERE Table1.AddressNumber BETWEEN Table2.AddressLowRange AND Table2.AddressHighRange
        AND Table1.AddressStreet = Table2.AddressStreet
        AND Table1.AddressZip = Table2.AddressZip
    )
;

Answer 4

I'm assuming you want to consider the number range, unlike most of the other answers. Something like this should work:

select Id
  from Table1
 where Id not in (select T1.Id
                    from Table1 T1
                    join Table2 T2 on (T1.AddressNumber between T2.AddressLowRange and T2.AddressHighRange)
                                   and T1.AddressStreet = T2.AddressStreet
                                   and T1.AddressZip = T2.AddressZip)

or slightly simplified...

select T1.Id
  from #table1 T1
  left join #table2 T2 on (T1.AddressNumber between T2.AddressLowRange and T2.AddressHighRange)
                      and T1.AddressStreet = T2.AddressStreet
                      and T1.AddressZip = T2.AddressZip
 where T2.AddressLowRange is null