comparing two fields

Greetings.

I am trying to write a sql that identifies all the rows in one table that do not match another. The sql needs to compare the values in two columns of each table. In the past, I've used a where not exists and a subquery to identify missing rows.

select a.block_code

from BLKPTS_ADDXY a

where not exists

(select block_code

from allblkzips as s

where s.block_code = a.block_code)

I need to add a second column from each table (zip) to the query. For some reason, it doesn't seem as straightforward as I thought it should be. Suggestions are appreciated.

Thanks.

alan

If you are using SQL 2005, try a EXCEPT JOIN. Something like:

SELECT Block_Code, Zip
FROM BLKPTS_ADDXY
EXCEPT
SELECT Block_Code, Zip
FROM allblkzips

|||I usually use a not in clause

I m pretty sure this will work..you can concat the two columns thus,

select convert(varchar(10),a.block_code)+convert(varchar(10),a.second_code), <anything else you want to select>

from BLKPTS_ADDXY a

where convert(varchar(10),a.block_code)+convert(varchar(10),a.second_code) not in

(

selectconvert(varchar(10),s.block_code)+convert(varchar(10),s.second_code)

from allblkzips as s

)

This will probably run faster than the query you are using which tries to run a where condition in an inner query with a field in the outer query, comparable with a for followed by another v/s a nested for loop in procedural programming.|||

Do either of the queries below return the results you are expecting?

Chris

SELECT a.block_code

FROM BLKPTS_ADDXY a

WHERE NOT EXISTS

(SELECT block_code

FROM allblkzips s

WHERE s.block_code = a.block_code

AND s.zip = a.zip)

SELECT a.block_code

FROM BLKPTS_ADDXY a

WHERE NOT EXISTS

(SELECT block_code

FROM allblkzips s

WHERE s.block_code = a.block_code)

OR NOT EXISTS

(SELECT block_code

FROM allblkzips s

WHERE s.zip = a.zip)

|||

I prefer the "outer join" method, since it helps enforce thinking in terms of INNER and OUTER JOINs.

select

a.block_code

from BLKPTS_ADDXY a

left outer join allblkzips s

on a.block_code = s.block_code and

a.zip = s.zip

where (s.zip is null) -- you only need to specify one column here, since the JOIN fails if both do not match

Dan