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-01-20 : 11:34:08

Hi,

I have the following select statement;

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)

The problem is it still gives values when

dbo.TblAuto_coll.Prac_no <> dbo.QryManualRequest.prac_no

For example, 17 & 91 are present in QryManualRequest and dbo.TblAuto_coll, therefore, I dont expect them..

Results

Prac_no
37
10
17
91
44

How can I update this query to receive:

Prac_no
37
10
44

Thank u

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-20 : 12:02:07

Would this work for you? I am assuming you have additional columns in your select list - otherwise you wouldn't need to join to dbo.Practice

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 * FROM dbo.QryManualRequest 
	 WHERE dbo.TblAuto_coll.Prac_no = dbo.QryManualRequest.prac_no
)

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-01-20 : 12:18:59

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources