| Author |
 Topic  |
|
|
nigelc
Starting Member
United Kingdom
20 Posts |
 Posted - 12/08/2006 : 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
United Kingdom
20 Posts |
Posted - 12/08/2006 : 05:13:49
|
| To give more info principals.name is the user name. |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 12/08/2006 : 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 Larsson
Helsingborg, Sweden |
|
|
|
nigelc
Starting Member
United Kingdom
20 Posts |
Posted - 12/08/2006 : 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 NAME
86004 User1
23005 user2
29354 User3
29725 User4
27948 User5
An example of the data in the SCOS table:
SCO_ID FOLDER_ID NAME
19447 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
_ID
46668 46464 28573 P 07/03/2006 08:20:14
21513 16550 10374 P 29/09/2005 13:26:28
23762 22351 13274 P 16/10/2005 09:01:20
23409 23055 10017 P 13/10/2005 13:14:05
45950 43540 29939 P 01/03/2006 14:19:22
45949 32222 29939 P 01/03/2006 14:19:22
23474 21559 23421 P 13/10/2005 18:56:30
45882 43540 29929 P 01/03/2006 14:09:41
The output I am looking for is a list of all the name of users who have not sat this module.
|
|
|
|
snSQL
Flowing Fount of Yak Knowledge
USA
1837 Posts |
Posted - 12/08/2006 : 12:43:20
|
Your query is wrong because you are comparing the wrong columns - pps_principals.name and pps_scos.name.
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-%')
But it should be as simple as (I leave you to put in the other columns and filters
SELECT *
FROM PPS_PRINCIPALS
WHERE NOT EXISTS (SELECT *
FROM PPS_TRANSCRIPTS
WHERE PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID)
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 12/08/2006 : 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 Larsson
Helsingborg, Sweden |
|
|
|
nigelc
Starting Member
United Kingdom
20 Posts |
Posted - 12/11/2006 : 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 ASC
But get the following error:
Server: Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'WHERE'.
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 12/11/2006 : 06:24:28
|
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 ASC
Madhivanan
Failing to plan is Planning to fail |
|
|
|
nigelc
Starting Member
United Kingdom
20 Posts |
Posted - 12/11/2006 : 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 ASC
Get the following error:
Server: Msg 1013, Level 15, State 1, Line 6
Tables or functions 'PPS_PRINCIPALS' and 'PPS_PRINCIPALS' have the same exposed names. Use correlation names to distinguish them.
|
|
|
|
snSQL
Flowing Fount of Yak Knowledge
USA
1837 Posts |
Posted - 12/11/2006 : 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 be
FROM PPS_SCOS
That'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
United Kingdom
20 Posts |
Posted - 12/12/2006 : 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 names
principals lists users names
The 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 TABLE
TRANSCRIPT_ID SCO_ID ASSET_ID PRINCIPAL_ID SESSION_ID TICKET STATUS
10025 10019 10022 10010024 bz7q9px9cn8n C
10026 10019 10022 10017 10010023 23is5y7f5nf7 C
10027 10019 10022 10017 10010023 54epct3cb8ui C
10121 10116 10118 10101 10010115 3i77qpzqzs42 C
10126 10116 10118 10101 10010115 id6wuqwrt9id C
SCOS TABLE
SCO_ID ACCOUNT_ID FOLDER_ID NAME TYPE ICON
19447 7 19432 MTM-065 Driving Safety Module 1 - Driving whilst Intoxicated 15 1
46481 7 41601 MTM-065 Driving Safety Module 1 - Driving whilst Intoxicated 15 1
46461 7 13611 MTM-065 Driving Safety Module 1 - Driving whilst Intoxicated 0 1
46364 7 25562 MTM-065 Driving Safety Module 1 - Driving Whilst Intoxicated 0 1023
64801 7 64800 MTM-065 Driving Safety Module 1 - Driving whilst Intoxicated 15 1
68362 7 26303 MTM-065 Driving Safety Module 1 - Driving whilst Intoxicated 15 1
PRINCIPALS TABLE
PRINCIPAL_ID ACCOUNT_ID NAME
82534 7 User1
29724 7 User2
86004 7 User3
23005 7 User4
29354 7 User5
Hope this makes sense.
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 12/12/2006 : 05:26:39
|
select p.* from principals p
where not exist (select null from transcripts t where t.principal_id = p.principal_id)
Peter Larsson
Helsingborg, Sweden |
|
|
|
nigelc
Starting Member
United Kingdom
20 Posts |
Posted - 12/12/2006 : 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 Again
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 ASC |
|
|
| |
Topic |
|
Comparing Results In Tables
