SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Comparing Results In Tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nigelc
Starting Member

United Kingdom
20 Posts

Posted - 12/08/2006 :  05:10:12  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
To give more info principals.name is the user name.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30188 Posts

Posted - 12/08/2006 :  08:19:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

nigelc
Starting Member

United Kingdom
20 Posts

Posted - 12/08/2006 :  08:46:35  Show Profile  Reply with Quote
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.
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 12/08/2006 :  12:43:20  Show Profile  Reply with Quote
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)

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30188 Posts

Posted - 12/08/2006 :  14:10:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

nigelc
Starting Member

United Kingdom
20 Posts

Posted - 12/11/2006 :  06:07:40  Show Profile  Reply with Quote
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'.

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 12/11/2006 :  06:24:28  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

nigelc
Starting Member

United Kingdom
20 Posts

Posted - 12/11/2006 :  06:27:10  Show Profile  Reply with Quote
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.



Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 12/11/2006 :  11:57:38  Show Profile  Reply with Quote
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.
Go to Top of Page

nigelc
Starting Member

United Kingdom
20 Posts

Posted - 12/12/2006 :  04:27:37  Show Profile  Reply with Quote
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.



Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30188 Posts

Posted - 12/12/2006 :  05:26:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
select p.* from principals p
where not exist (select null from transcripts t where t.principal_id = p.principal_id)


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

nigelc
Starting Member

United Kingdom
20 Posts

Posted - 12/12/2006 :  06:11:01  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000