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?