Output results incorrect - 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

Output results incorrect

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-10-04 : 10:04:27

Hi,

I have the following query

SELECT DISTINCT dbo.TblAuto_coll.Prac_no, dbo.QryManualRequest.audit_start
FROM         dbo.TblAuto_coll INNER JOIN
                      dbo.QryManualRequest ON dbo.TblAuto_coll.Prac_no = dbo.QryManualRequest.prac_no INNER JOIN
                      [GPRD-RCT].dbo.Practice ON dbo.TblAuto_coll.Prac_no <> [GPRD-RCT].dbo.Practice.PracticeCode

TblAuto_coll
15
27
39

[GPRD-RCT].dbo.Practice.PracticeCode
15
27
40
50

dbo.QryManualRequest

15
27
39

The above qurry results to:
prac_no
15
27
39

I wish the results to be:

prac_no
39

any ideas thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 11:35:39

[code]
SELECT DISTINCT dbo.TblAuto_coll.Prac_no, dbo.QryManualRequest.audit_start
FROM dbo.TblAuto_coll INNER JOIN
dbo.QryManualRequest ON dbo.TblAuto_coll.Prac_no = dbo.QryManualRequest.prac_no LEFT JOIN
[GPRD-RCT].dbo.Practice ON dbo.TblAuto_coll.Prac_no = [GPRD-RCT].dbo.Practice.PracticeCode
WHERE [GPRD-RCT].dbo.Practice.PracticeCode IS NULL
[/code]

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

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-10-04 : 11:46:46

it worked perfectly - just for my knowledge

why did you include the WHERE clause as
WHERE [GPRD-RCT].dbo.Practice.PracticeCode IS NULL

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 11:50:53

that will make sure you're returning only rows that are not present in [GPRD-RCT].dbo.Practice.
LEFT JOIN will return all rows from left without looking for match with [GPRD-RCT].dbo.Practice. All ones having matching values will bring them and all others will have NULL as values for [GPRD-RCT].dbo.Practice fields. so checking for NULLs will ensure we filter only rows not present in [GPRD-RCT].dbo.Practice

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources