Select Statement - 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

General SQL Server Forums

New to SQL Server Programming

Select Statement

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-10-29 : 08:29:03

Hi,

I have th efollowing query;

SELECT DISTINCT dbo.TblAuto_coll.Prac_no AS UnProc_Prac_no
FROM         dbo.TblAuto_coll INNER JOIN
                      dbo.QryManualRequest ON dbo.TblAuto_coll.Prac_no <> dbo.QryManualRequest.prac_no LEFT OUTER JOIN
                      [GPRD-RCT].dbo.Practice ON dbo.TblAuto_coll.Prac_no = [GPRD-RCT].dbo.Practice.PracNo
WHERE     ([GPRD-RCT].dbo.Practice.PracNo IS NULL) AND (NOT EXISTS
                          (SELECT     prac_no, sub_media, MaxColDate, audit_start
                            FROM          dbo.QryManualRequest AS QryManualRequest_1
                            WHERE      (dbo.TblAuto_coll.Prac_no = prac_no)))

TblAuto_coll

Prac_no col_status
433 S
136 I

QryManualRequest

Prac_no
10
15
46

GPRD-RCT.dbo (Practice)

PracNo
433

Now my result should be;

UnProc_Prac_No
433 (doesn't exist in QryManualRequest)
136 (doesn't exist in QryManualRequest)

However, on the above query I receive;

UnProc_Prac_No
136

433 is not included..

Please help..
Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-29 : 08:52:58

It is the first condition in your WHERE clause that is eliminating 344. Remove that:

SELECT DISTINCT dbo.TblAuto_coll.Prac_no AS UnProc_Prac_no
FROM         dbo.TblAuto_coll INNER JOIN
                      dbo.QryManualRequest ON dbo.TblAuto_coll.Prac_no <> dbo.QryManualRequest.prac_no LEFT OUTER JOIN
                      [GPRD-RCT].dbo.Practice ON dbo.TblAuto_coll.Prac_no = [GPRD-RCT].dbo.Practice.PracNo
WHERE     ([GPRD-RCT].dbo.Practice.PracNo IS NULL) AND (NOT EXISTS
                          (SELECT     prac_no, sub_media, MaxColDate, audit_start
                            FROM          dbo.QryManualRequest AS QryManualRequest_1
                            WHERE      (dbo.TblAuto_coll.Prac_no = prac_no)))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-29 : 09:35:07

if thats your requirement I cant find a reason for including that LEFT JOIN with Practise table at all. Perhaps you could dispense with that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources