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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Skittlewidth
Forum Contributor
Posts: 389
Joined: Wed Nov 06, 2002 9:18 am
Location: Kent, UK

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

Post 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?
Last edited by Skittlewidth on Thu Mar 15, 2007 4:50 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

What's the table structure for vwJobs?
User avatar
Skittlewidth
Forum Contributor
Posts: 389
Joined: Wed Nov 06, 2002 9:18 am
Location: Kent, UK

Post 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.
User avatar
Skittlewidth
Forum Contributor
Posts: 389
Joined: Wed Nov 06, 2002 9:18 am
Location: Kent, UK

Post 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
Post Reply