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.
Author |
Topic |
nigelc
Starting Member
20 Posts |
Posted - 2006-12-11 : 07:06:22
|
I have a query that counts the number of training modules a user has sat. This works fine but I want it to be able to list the names aof all users who have not yet sat a course. Query I am using is:SELECT pps_principals.name AS principals_name,COUNT(*) AS coursecount FROM (PPS_SCOS JOIN PPS_TRANSCRIPTS ON PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID)JOIN PPS_PRINCIPALS ON PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID AND PPS_TRANSCRIPTS.TICKET not like 'l-%'and pps_scos.name like 'MT%'GROUP BY pps_principals.nameHAVING COUNT(*) = 0The principals.name is the user name. Scos.name being the course name. This query returns no rows at all. |
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-12-11 : 07:15:31
|
[code]SELECT pps_principals.name AS principals_nameFROM PPS_PRINCIPALS join PPS_TRANSCRIPTS on PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID Left Join PPS_SCOS on PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_IDWhere PPS_SCOS.SCO_ID is NULL and AND PPS_TRANSCRIPTS.TICKET not like 'l-%' and pps_scos.name like 'MT%'[/code]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
nigelc
Starting Member
20 Posts |
Posted - 2006-12-11 : 07:20:24
|
I have tried this but it still comes back with no names.Thanks |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-12-11 : 07:22:13
|
It would be helpful if you could post table structures and their relationships.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|