SQL Query from 2 tables

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

Tabl开发者_C百科e 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.

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

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

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

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