Page 1 of 1

[Solved] Getting unique ids - distinct or group by?

Posted: Wed Mar 14, 2007 9:46 am
by Skittlewidth
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.

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.intRegionId
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?

Posted: Wed Mar 14, 2007 9:57 am
by feyd
What's the table structure for vwJobs?

Posted: Wed Mar 14, 2007 10:04 am
by Skittlewidth
The sql for the dyJobs table that vwJobs is a view of is (courtesy of MSSQL):

Code: Select all

CREATE TABLE [dbo].[dyJobs] (
	[intID] [int] IDENTITY (1, 1) NOT NULL ,
	[intJobCategoryID] [int] NULL ,
	[intRoleTypeID] [int] NULL ,
	[intJobTypeID] [int] NULL ,
	[intPositionID] [int] NULL ,
	[strPositionName] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[strTitle] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[strDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[strBenefits] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[intLocationID] [int] NULL ,
	[intSalaryFrom] [int] NULL ,
	[intSalaryTo] [int] NULL ,
	[intAccountHandlerID] [int] NULL ,
	[strReferenceNumber] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[booVisible] [bit] NULL ,
	[datCreated] [datetime] NULL ,
	[TEMP_title] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TEMP_consultant] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TEMP_location] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TEMP_type] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TEMP_categories] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
and the join to create vwJobs is:

Code: Select all

SELECT     dbo.dyJobs.*, dbo.lnkRoleTypes.intRoleType AS intRoleTypeLnk
FROM         dbo.dyJobs INNER JOIN
                      dbo.lnkRoleTypes ON dbo.dyJobs.intID = dbo.lnkRoleTypes.intJobId
which just creates an additional role type id column and causes the duplicates.

Posted: Thu Mar 15, 2007 4:50 am
by Skittlewidth
Took a fresh look at it this morning and it was obvious what I needed to do - work out exactly what fields were needed for the webpage and select them explicitly, thus eliminating the problematic fields that Enterprise manager was complaining about. The annoying thing is that I knew this yesterday, I was just going the wrong way about it. Anyway, modified query is as follows:

Code: Select all

SELECT DISTINCT 
                      vwJobs.intID, vwJobs.strPositionName, vwJobs.strTitle, vwJobs.intSalaryFrom, vwJobs.intSalaryTo, 
                      luSubRegion.strName AS strLocation
FROM         vwJobs LEFT OUTER JOIN                  
                      luSubRegion ON luSubRegion.intId = vwJobs.intLocationID LEFT OUTER JOIN
                      luRegion ON luRegion.intID = luSubRegion.intRegionId