NOT IN, NOT EXISTS, EXCEPT problem

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 2005 Forums

Transact-SQL (2005)

NOT IN, NOT EXISTS, EXCEPT problem

Author

Topic

vNz
Starting Member

1 Post

Posted - 2008-02-22 : 04:37:27

Hi everybody,
I am trying to get this query working :

SELECT DISTINCT ExPOId
FROM dbo.PurcHdr, dbo.PurcDet
WHERE dbo.PurcHdr.InPurcId = dbo.PurcDet.ExPurcId
AND PurcType = 2
AND NOT EXISTS (
SELECT DISTINCT ExPOId
FROM dbo.PurcDet, dbo.PurcHdr
WHERE dbo.PurcHdr.InPurcId = dbo.PurcDet.ExPurcId and (PurcType = 3)
)

But I get a blank result, even with ExPOId NOT IN... I actually want all the ExPoId of type 2 that doesn't appear in ExPoId of type 3.
I checked my queries separately and I know that there must be values in the result.
Can someone help me?

The thing is that I can't use an EXCEPT command because I need to put that query in a view.

Thanks,
Vincent.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-02-22 : 05:30:29

try:
SELECT DISTINCT ExPOId
FROM dbo.PurcHdr inner join dbo.PurcDet
ON dbo.PurcHdr.InPurcId = dbo.PurcDet.ExPurcId
where ExPOId NOT IN (SELECT DISTINCT ExPOId FROM dbo.PurcDet Inner JOIN dbo.PurcHdr
ON dbo.PurcHdr.InPurcId = dbo.PurcDet.ExPurcId where PurcType = 3) and PurcType = 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-22 : 06:19:29

or:-

SELECT DISTINCT ph.ExPOId
FROM dbo.PurcHdr ph
JOIN  dbo.PurcDet pd
ON ph.InPurcId = pd.ExPurcId 
AND ph.PurcType = 2
LEFT JOIN (
SELECT DISTINCT ExPOId
FROM dbo.PurcDet pd
JOIN dbo.PurcHdr ph
ON ph.InPurcId = pd.ExPurcId 
and (ph.PurcType = 3)
)tmp
ON tmp.ExPOId=ph.ExPOId
WHERE tmp.ExPOId IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources