Error on join - 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

General SQL Server Forums

New to SQL Server Programming

Error on join

Author

Topic

nigelc
Starting Member

20 Posts

Posted - 2007-01-23 : 10:53:22

The following query gives a list of users who have completed 0 modules. Code below:

SELECT pps_principals.name AS pname,
COUNT(PPS_SCOS.SCO_ID) AS coursecount
FROM PPS_PRINCIPALS
LEFT JOIN PPS_TRANSCRIPTS ON PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID
AND PPS_TRANSCRIPTS.STATUS like '[PCF]'
AND PPS_TRANSCRIPTS.TICKET not like 'l-%'
AND pps_transcripts.date_created between '2006-10-01' and '2007-09-30'
LEFT JOIN PPS_SCOS ON PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID
AND pps_scos.name like 'MT%'
WHERE pps_principals.login like '%score%'
AND dbo.PPS_PRINCIPALS.DISABLED IS NULL
GROUP BY PPS_PRINCIPALS.NAME
HAVING COUNT(PPS_SCOS.SCO_ID) = 0
ORDER BY pps_principals.name asc

This query works fine.

I want to be able to do another join to a table called EXT_USER_GROUPS to the query. This ties in the names to a group based on location. I have added this (see code below):

SELECT pps_principals.name AS pname, EXT_USER_GROUPS.LOGIN,, EXT_USER_GROUPS.NAME,
COUNT(PPS_SCOS.SCO_ID) AS coursecount
FROM PPS_PRINCIPALS
LEFT JOIN PPS_TRANSCRIPTS ON PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID
AND PPS_TRANSCRIPTS.STATUS like '[PCF]'
AND PPS_TRANSCRIPTS.TICKET not like 'l-%'
AND pps_transcripts.date_created between '2006-10-01' and '2007-09-30'
LEFT JOIN PPS_SCOS ON PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID
LEFT JOIN EXT_USER_GROUPS ON EXT_USER_GROUPS.LOGIN = PPS_PRINCIPALS.LOGIN
AND pps_scos.name like 'MT%'
WHERE pps_principals.login like '%TEST%'
AND dbo.PPS_PRINCIPALS.DISABLED IS NULL
GROUP BY PPS_PRINCIPALS.NAME
HAVING COUNT(PPS_SCOS.SCO_ID) = 0
ORDER BY pps_principals.name asc

With this I get the following error:

Server: Msg 8120, Level 16, State 1, Line 1
Column 'EXT_USER_GROUPS.LOGIN' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Any help appreciated.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-23 : 11:01:17

I think the message you got is crystal clear. You need to add new columns to the GROUP BY clause as well.

SELECT pps_principals.name AS pname, EXT_USER_GROUPS.LOGIN, EXT_USER_GROUPS.NAME,
COUNT(PPS_SCOS.SCO_ID) AS coursecount 
FROM PPS_PRINCIPALS
LEFT JOIN PPS_TRANSCRIPTS ON PPS_TRANSCRIPTS.PRINCIPAL_ID = PPS_PRINCIPALS.PRINCIPAL_ID
AND PPS_TRANSCRIPTS.STATUS like '[PCF]'
AND PPS_TRANSCRIPTS.TICKET not like 'l-%'
AND pps_transcripts.date_created between '2006-10-01' and '2007-09-30'
LEFT JOIN PPS_SCOS ON PPS_SCOS.SCO_ID = PPS_TRANSCRIPTS.SCO_ID
LEFT JOIN EXT_USER_GROUPS ON EXT_USER_GROUPS.LOGIN = PPS_PRINCIPALS.LOGIN
AND pps_scos.name like 'MT%' 
WHERE pps_principals.login like '%TEST%'
AND dbo.PPS_PRINCIPALS.DISABLED IS NULL
GROUP BY pps_principals.name, EXT_USER_GROUPS.LOGIN, EXT_USER_GROUPS.NAME
HAVING COUNT(PPS_SCOS.SCO_ID) = 0
ORDER BY pps_principals.name, EXT_USER_GROUPS.LOGIN, EXT_USER_GROUPS.NAME

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nigelc
Starting Member

20 Posts

Posted - 2007-01-23 : 11:24:48

Harsh,

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources