[Solved] Getting unique ids - distinct or group by?
Posted: Wed Mar 14, 2007 9:46 am
I had to make a modification to an existing MSSQL database so that clients could have a one to many relationship between a job vacancy and the role categories that the job could fall under.
I set up a view table so that I wouldn't have to change the search system on the website too much, by joining the lnkRoleTypes link table to the original dyJobs table thus creating vwJobs. This of course created rows where a job id was duplicated to account for having multiple roleType Ids.
Now I need the website search to only display each Job Id once, because I've now got a situation where its returning the same job up to 5 times in a row and I can't get distinct or group by to work, possibly because the following query isn't refined enough.
If I add "group by vwJobs.intID" SQL Enterprise manager says "Column 'vwJobs.intJobCategoryID' is invalid in the select list because it is not contained in either an aggregate function or the group by clause"
Well I don't really know how the intJobCategoryID came in to it, but can anyone tell me what I've done wrong?
I set up a view table so that I wouldn't have to change the search system on the website too much, by joining the lnkRoleTypes link table to the original dyJobs table thus creating vwJobs. This of course created rows where a job id was duplicated to account for having multiple roleType Ids.
Now I need the website search to only display each Job Id once, because I've now got a situation where its returning the same job up to 5 times in a row and I can't get distinct or group by to work, possibly because the following query isn't refined enough.
Code: Select all
SELECT vwJobs.*, luSubRegion.strName AS strLocation
FROM vwJobs LEFT OUTER JOIN
luSubRegion ON luSubRegion.intId = vwJobs.intLocationID LEFT OUTER JOIN
luRegion ON luRegion.intID = luSubRegion.intRegionIdWell I don't really know how the intJobCategoryID came in to it, but can anyone tell me what I've done wrong?