Query Comparisons - 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

Query Comparisons

Author

Topic

nigelc
Starting Member

20 Posts

Posted - 2007-02-15 : 04:20:55

I have the following query below that I am trying to get working. What I want it to do is check for users who have sat a module and failed it and compare it to a table to check that they have not passed the module second time and report only those who have failed withg no passes. Query below.

SELECT DISTINCT dbo.PPS_SCOS.NAME, PPS_PRINCIPALS.NAME, pps_transcripts.date_created, score, max_score, status
FROM (dbo.PPS_SCOS JOIN dbo.PPS_TRANSCRIPTS ON dbo.PPS_SCOS.SCO_ID = dbo.PPS_TRANSCRIPTS.SCO_ID)
JOIN dbo.PPS_PRINCIPALS ON dbo.PPS_TRANSCRIPTS.PRINCIPAL_ID = dbo.PPS_PRINCIPALS.PRINCIPAL_ID
WHERE dbo.PPS_SCOS.NAME LIKE 'MTB-S001%'
AND PPS_PRINCIPALS.LOGIN LIKE '%test%'
AND dbo.PPS_TRANSCRIPTS.STATUS LIKE 'F'
AND PPS_TRANSCRIPTS.TICKET not like 'l-%'
AND dbo.PPS_PRINCIPALS.NAME NOT IN (
SELECT DISTINCT dbo.PPS_SCOS.NAME FROM (dbo.PPS_SCOS JOIN dbo.PPS_TRANSCRIPTS ON dbo.PPS_SCOS.SCO_ID = dbo.PPS_TRANSCRIPTS.SCO_ID)
JOIN dbo.PPS_PRINCIPALS ON dbo.PPS_TRANSCRIPTS.PRINCIPAL_ID = dbo.PPS_PRINCIPALS.PRINCIPAL_ID
WHERE dbo.PPS_TRANSCRIPTS.STATUS LIKE 'P'
AND dbo.PPS_SCOS.NAME LIKE 'MTB-S001%'
AND PPS_PRINCIPALS.LOGIN LIKE '%test%'
AND PPS_TRANSCRIPTS.TICKET not like 'l-%' )
ORDER BY pps_PRINCIPALS.NAME

Any help appreciated.

Thanks

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-02-15 : 10:02:06

Use NOT EXISTS rather than NOT IN for your test and use the primary key value for the user rather than the user name to relate the queries.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources