Problems w/ Exists and not Exists

[strangeBrew12]

I am trying to run a simple query using the not exists, but it is not work. I forced in a test record in the DFAddress table that is not in the Address table. Below is the query I am using...

select AddrType, AddrLine1, AddrLine2, AddrLine3, AddrLine4, AddrCity,
AddrState, AddrZipC, AddrCountry, AddrEntryPerson
From Address
where exists
(SELECT AddrType, AddrLine1, AddrLine2, AddrLine3, AddrLine4, AddrCity,
AddrState, AddrZipC, AddrCountry, AddrEntryPerson
FROM DFAddress)

Maybe there is an easier way to handle this? Better method?The tables are not identical in their full form, so the * would not work (I think)

Thanks all!

JJ

 

[gmmastros]

The exists function essentially returns a boolean. If there are any matches, then it returns true. Since there is not where clause, as long as there is 1 record in the DFAddress table, Exists will return true.

Are you trying to find records in the address table that do not exist in the DFAddress table?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[strangeBrew12]

Thanks for the direction. I needed to add a Where clause in the Not Exists portion to fix things.

All seems right with the world again.

Thanks!

JJ

 

[gmmastros]

To find addresses that exist in the DFAddress table but NOT in the Address table:

Select D.*
From   DFAddress As D
       Left Join Address As A
         On  D.AddrType = A.AddrType
         And D.AddrLine1 = A.AddrLine1
         And D.AddrLine2 = A.AddrLine2
         And D.AddrLine3 = A.AddrLine3
         And D.AddrLine4 = A.AddrLine4
         And D.AddrCity = A.AddrCity
         And D.AddrState = A.AddrState
         And D.AddrZipC = A.AddrZipC
         And D.AddrCountry = A.AddrCountry
         And D.AddrEntryPerson = A.AddrEntryPerson
Where  A.AddrType Is NULL

I may have misspellled some of the field names.

You need to be careful relying on this, though. Any goofy little thing can cause this to 'not match'. For example, 1 table may have 'Main St' and the other may have 'Main Street'.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

don't know your key columns but here are 2 ways, a third is using IN

select a.AddrType, a.AddrLine1, a.AddrLine2, a.AddrLine3, a.AddrLine4, a.AddrCity,
    a.AddrState, a.AddrZipC, a.AddrCountry, a.AddrEntryPerson
From     Address a
where not exists
(SELECT    * FROM DFAddress where a.AddrEntryPerson =  AddrEntryPerson -- add more columns here) 

select a.AddrType, a.AddrLine1, a.AddrLine2, a.AddrLine3, a.AddrLine4, a.AddrCity,
    a.AddrState, a.AddrZipC, a.AddrCountry, a.AddrEntryPerson
From     Address a
left join DFAddress d on a.AddrEntryPerson =  d.AddrEntryPerson
--and a.... = d.....
where d.AddrEntryPerson is null

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/