Author |
Topic |
nigelc
Starting Member
20 Posts |
Posted - 2006-12-08 : 05:10:12
|
I have a query form 3 tables joined together which gives me a list of name of individuals who have completed a training course. However I want a query that allows me to list all the names of those who have not sat the course. The query I am trying to get to work is below but it just keeps returning the results of those who have sat the course and not those who have not sat it. Any help would be appreciated.SELECT distinct pps_principals.name as pname ,pps_scos.name ,pps_transcripts.score ,pps_transcripts.max_score ,pps_transcripts.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 pps_transcripts.date_created between '2006-12-01' and '2006-12-31' AND PPS_TRANSCRIPTS.STATUS like 'P' and pps_scos.NAME = 'Course1' AND PPS_TRANSCRIPTS.TICKET not like 'l-%' AND pps_principals.name not in (SELECT distinct 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 pps_scos.name = 'Course1' AND pps_transcripts.date_created between '2006-12-01' and '2006-12-31' AND PPS_TRANSCRIPTS.TICKET not like 'l-%') |
|
nigelc
Starting Member
20 Posts |
Posted - 2006-12-08 : 05:13:49
|
To give more info principals.name is the user name. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-08 : 08:19:25
|
Give us some sample data, your expected output based on that sample data. Also give us some relevant DDL for the sample data.Peter LarssonHelsingborg, Sweden |
|
|
nigelc
Starting Member
20 Posts |
Posted - 2006-12-08 : 08:46:35
|
Peter,The expected output looks like this in the normal query. What I want is to be able to query all users who have not completed this course and print that list out, based on the NOT IN.An example of the data in the PRINCIPALS table:PRINCIPAL_ID NAME86004 User123005 user229354 User329725 User427948 User5An example of the data in the SCOS table:SCO_ID FOLDER_ID NAME19447 19432 Driving Safety 46481 41601 Driving Safety 46461 13611 Driving Safety 46364 25562 Driving Safety 64801 64800 Driving Safety An example of data in the transcripts table:TRANSCRIPT SCO_ID PRINCIPAL_ID STATUS DATE_CREATED_ID46668 46464 28573 P 07/03/2006 08:20:1421513 16550 10374 P 29/09/2005 13:26:2823762 22351 13274 P 16/10/2005 09:01:2023409 23055 10017 P 13/10/2005 13:14:0545950 43540 29939 P 01/03/2006 14:19:2245949 32222 29939 P 01/03/2006 14:19:2223474 21559 23421 P 13/10/2005 18:56:3045882 43540 29929 P 01/03/2006 14:09:41The output I am looking for is a list of all the name of users who have not sat this module. |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-08 : 12:43:20
|
Your query is wrong because you are comparing the wrong columns - pps_principals.name and pps_scos.name.SELECT distinctpps_principals.name as pname,pps_scos.name,pps_transcripts.score,pps_transcripts.max_score,pps_transcripts.statusFROM (dbo.PPS_SCOSJOIN dbo.PPS_TRANSCRIPTSON dbo.PPS_SCOS.SCO_ID = dbo.PPS_TRANSCRIPTS.SCO_ID)JOIN dbo.PPS_PRINCIPALSON dbo.PPS_TRANSCRIPTS.PRINCIPAL_ID = dbo.PPS_PRINCIPALS.PRINCIPAL_IDwhere pps_transcripts.date_created between '2006-12-01' and '2006-12-31'AND PPS_TRANSCRIPTS.STATUS like 'P'and pps_scos.NAME = 'Course1' AND PPS_TRANSCRIPTS.TICKET not like 'l-%'AND pps_principals.name not in (SELECT distinctpps_scos.name FROM(dbo.PPS_SCOSJOIN dbo.PPS_TRANSCRIPTSON dbo.PPS_SCOS.SCO_ID = dbo.PPS_TRANSCRIPTS.SCO_ID)JOIN dbo.PPS_PRINCIPALSON dbo.PPS_TRANSCRIPTS.PRINCIPAL_ID = dbo.PPS_PRINCIPALS.PRINCIPAL_IDWHERE pps_scos.name = 'Course1'AND pps_transcripts.date_created between '2006-12-01' and '2006-12-31'AND PPS_TRANSCRIPTS.TICKET not like 'l-%') But it should be as simple as (I leave you to put in the other columns and filtersSELECT * FROM PPS_PRINCIPALSWHERE NOT EXISTS (SELECT * FROM PPS_TRANSCRIPTS WHERE PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID) |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-08 : 14:10:01
|
Yeah! I saw that the original query was almost impossible to read and to interpret.That's why I asked for sample data.And look how easy the final query seems to be... Peter LarssonHelsingborg, Sweden |
|
|
nigelc
Starting Member
20 Posts |
Posted - 2006-12-11 : 06:07:40
|
Thanks, I am relatively new to SQL so still trying to feel my way about. Have restructured the query as follows:SELECT * FROM PPS_PRINCIPALS 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 AND pps_scos.NAME = 'MTM-065 Driving Safety - Module 1(DWI) QUIZ v1.1' AND PPS_TRANSCRIPTS.STATUS like 'P' AND PPS_TRANSCRIPTS.TICKET not like 'l-%' WHERE NOT EXISTS (SELECT * FROM (PPS_TRANSCRIPTS WHERE PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID) AND pps_scos.NAME = 'MTM-065 Driving Safety - Module 1(DWI) QUIZ v1.1' AND PPS_TRANSCRIPTS.TICKET not like 'l-%'ORDER BY NAME ASCBut get the following error:Server: Msg 156, Level 15, State 1, Line 12Incorrect syntax near the keyword 'WHERE'. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-12-11 : 06:24:28
|
[code]SELECT * FROM PPS_PRINCIPALS JOIN dbo.PPS_TRANSCRIPTSON dbo.PPS_SCOS.SCO_ID = dbo.PPS_TRANSCRIPTS.SCO_ID JOIN dbo.PPS_PRINCIPALSON dbo.PPS_TRANSCRIPTS.PRINCIPAL_ID = dbo.PPS_PRINCIPALS.PRINCIPAL_ID AND pps_scos.NAME = 'MTM-065 Driving Safety - Module 1(DWI) QUIZ v1.1' AND PPS_TRANSCRIPTS.STATUS like 'P' AND PPS_TRANSCRIPTS.TICKET not like 'l-%'WHERE NOT EXISTS ( SELECT * FROM PPS_TRANSCRIPTS WHERE PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID) AND pps_scos.NAME = 'MTM-065 Driving Safety - Module 1(DWI) QUIZ v1.1' AND PPS_TRANSCRIPTS.TICKET not like 'l-%'ORDER BY NAME ASC[/code]MadhivananFailing to plan is Planning to fail |
|
|
nigelc
Starting Member
20 Posts |
Posted - 2006-12-11 : 06:27:10
|
I have modified the statement as follows:SELECT * FROM PPS_PRINCIPALS 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_scos.NAME = 'MTM-065 Driving Safety - Module 1(DWI) QUIZ v1.1' AND PPS_TRANSCRIPTS.STATUS like 'P' AND PPS_TRANSCRIPTS.TICKET not like 'l-%' WHERE NOT EXISTS (SELECT * FROM PPS_TRANSCRIPTS WHERE PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID AND pps_scos.NAME = 'MTM-065 Driving Safety - Module 1(DWI) QUIZ v1.1' AND PPS_TRANSCRIPTS.TICKET not like 'l-%')ORDER BY NAME ASCGet the following error:Server: Msg 1013, Level 15, State 1, Line 6Tables or functions 'PPS_PRINCIPALS' and 'PPS_PRINCIPALS' have the same exposed names. Use correlation names to distinguish them. |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-12-11 : 11:57:38
|
You still haven't posted any data or a description of what you really want the query to do, so we cannot do more than point out the fix for each syntax error.The error in this case is pretty much telling you that you have two tables with the same name, which is your case is the error, FROM PPS_PRINCIPALS should beFROM PPS_SCOSThat's not to say that you cannot use a table more than once in a query but I won't get into that because it's not what you're trying to do here. |
|
|
nigelc
Starting Member
20 Posts |
Posted - 2006-12-12 : 04:27:37
|
I have ran a query which tells me how many people have sat a particular training course in this case a Driving Safety Course. This works OK. I then wanted to be able to run a query to be able to produce a list of all users who have not sat this training course. The DB contains all the info I need in 3 tables pps_principals, pps_scos, and pps_transcripts.scos lists the course namesprincipals lists users namesThe DB itself is not the most user friendly and the queries I am running are to produce statistical data I have only read access.I was using NOT EXISTS to try and get a list of the names of those having not completed the course.I have included some code from the 3 tables below. This is not all the columns as there are too many.TRANSCRIPTS TABLETRANSCRIPT_ID SCO_ID ASSET_ID PRINCIPAL_ID SESSION_ID TICKET STATUS10025 10019 10022 10010024 bz7q9px9cn8n C10026 10019 10022 10017 10010023 23is5y7f5nf7 C10027 10019 10022 10017 10010023 54epct3cb8ui C10121 10116 10118 10101 10010115 3i77qpzqzs42 C10126 10116 10118 10101 10010115 id6wuqwrt9id CSCOS TABLESCO_ID ACCOUNT_ID FOLDER_ID NAME TYPE ICON19447 7 19432 MTM-065 Driving Safety Module 1 - Driving whilst Intoxicated 15 146481 7 41601 MTM-065 Driving Safety Module 1 - Driving whilst Intoxicated 15 146461 7 13611 MTM-065 Driving Safety Module 1 - Driving whilst Intoxicated 0 146364 7 25562 MTM-065 Driving Safety Module 1 - Driving Whilst Intoxicated 0 102364801 7 64800 MTM-065 Driving Safety Module 1 - Driving whilst Intoxicated 15 168362 7 26303 MTM-065 Driving Safety Module 1 - Driving whilst Intoxicated 15 1PRINCIPALS TABLEPRINCIPAL_ID ACCOUNT_ID NAME82534 7 User129724 7 User286004 7 User323005 7 User429354 7 User5Hope this makes sense. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-12 : 05:26:39
|
select p.* from principals pwhere not exist (select null from transcripts t where t.principal_id = p.principal_id)Peter LarssonHelsingborg, Sweden |
|
|
nigelc
Starting Member
20 Posts |
Posted - 2006-12-12 : 06:11:01
|
Peter,Thanks. I have tried to incorporate the changes you suggested but not having much success. As I am new to this could you please advise where to put the code in thqat you suggested. Original query below.Thanks AgainSELECT * FROM PPS_principalsJOIN PPS_TRANSCRIPTSON PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_IDJOIN PPS_PRINCIPALSON PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_IDAND pps_scos.NAME = 'MTM-065 Driving Safety - Module 1(DWI) QUIZ v1.1' AND PPS_TRANSCRIPTS.STATUS like 'P'AND PPS_TRANSCRIPTS.TICKET not like 'l-%'WHERE NOT EXISTS (SELECT *FROM PPS_TRANSCRIPTSWHERE PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_IDAND pps_scos.NAME = 'MTM-065 Driving Safety - Module 1(DWI) QUIZ v1.1' AND PPS_TRANSCRIPTS.TICKET not like 'l-%')ORDER BY NAME ASC |
|
|
|