not matching query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

not matching query

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-10-07 : 07:49:18

Hi, I am trying to show all establishments that do not exist in tblschemeest table where schemeId is a number.

schemeest table consists of schemeID and estID.
my current query brings back 0 results..

SELECT tblEstablishment.estid, tblEstablishment.dfee, tblEstablishment.name, tblEstablishment.address1, tblEstablishment.address2, tblEstablishment.town, tblEstablishment.county, tblEstablishment.postcode, tblEstablishment.telephone, tblEstablishment.EstType
FROM tblEstablishment LEFT JOIN tblSchemeEst ON tblEstablishment.estid = tblSchemeEst.EstID
WHERE (((tblSchemeEst.EstID) Is Null)) AND tblSchemeEST.SchemeID=2;

how can I get this to work /
hope this makes sense..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-07 : 07:54:37

Try this

Select T.* -- Replace * by columns

from tblEstablishment T where not exists
(Select * from tblSchemeEst where SchemeID =2 and estid =T.estid )

Madhivanan

Failing to plan is Planning to fail

jamie
Aged Yak Warrior

542 Posts

Posted - 2005-10-07 : 08:00:32

genius.

thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources