Comparing Results In Tables - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Comparing Results In Tables

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 Larsson
Helsingborg, 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 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
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 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

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 Larsson
Helsingborg, 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 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

22864 Posts

Posted - 2006-12-11 : 06:24:28

[code]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
[/code]

Madhivanan

Failing 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 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
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 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

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

30421 Posts

Posted - 2006-12-12 : 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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources