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
 Transact-SQL (2000)
 Comparing queries

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-12-12 : 07:35:14
nigel cordiner writes "I am trying to formualate a query that shows a list of names of users who have not completed a particular training course. It compares this to a list of names of those who have completed the course to then list the names of those having not completed.

The query I am using is below:

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' query tha
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

However I am getting the following error:

Server: Msg 1013, Level 16, State 2, Line 1
Tables or functions 'PPS_PRINCIPALS' and 'dbo.PPS_PRINCIPALS' have the same exposed names. Use correlation names to distinguish them.

Using Microsoft SQL Server 2000 - 8.00.2040 (Intel X86) May 13 2005 18:33:17 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)


Any help appreciated."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 07:45:00
Duplicate post of
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76181
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76088


Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

- Advertisement -