Query Issue??

[zishan876]

Hi I have the following:

SELECT account, name, paidAmount,Id,lob,creditStatus   FROM adsBostonGlobeAdvert a WHERE NOT EXISTS  (SELECT * FROM Master m WHERE a.account = m.account      AND desk NOT IN ('PUR', 'WJMPURGE')      AND current0 <> 0)

I am trying to say give me accounts that are in A and not Exist in M
...
For some reason I am getting accounts that exist in both files...
Can someone please tell me where I went wrong...
Thanks

 

[SQLSister]

Don't use not exists use a left join instead.

SELECT account, name, paidAmount,Id,lob,creditStatus   
FROM adsBostonGlobeAdvert a 
left join Master m on a.account = m.account     
and desk NOT IN ('PUR', 'WJMPURGE')      AND current0 <> 0
where m.account is null

This assumes that the fields desk and current0 are in Master. (BTW, master is a really poor name for a table as it is the name of a system database, you really should avoid doing things like that for clarity.)

"NOTHING is more important in a database than integrity." ESquared

 

[jbenson001]

Try using a Left Join:

SELECT account, name, paidAmount,Id,lob,creditStatus
FROM adsBostonGlobeAdvert a
   LEFT Join Master m ON a.account = m.account
Where m.account IS NULL