Please help get rid of this duplicate value!

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

SQL Server 2000 Forums

Transact-SQL (2000)

Please help get rid of this duplicate value!

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-06-23 : 06:04:50

I have a database that assigns skills to users. Skills are assigned to skill groups, and then users are also assigned to skill groups, both using a seperate table to store the allocations.

The problem is that a skill can exist in more than one group. The following statement searches to find all the skills assigned to 1 user via the allocation tables. However, where a skill appears in multiple groups that the user is allocated to, the results show that skill each time.

Can the following select statement be amended to only show 1 occurrence of the tbl_Skills.skillID field?


SELECT
	tbl_Skills.skillID,  -- <-- This one should only appear once per search
	tbl_Skills.skillDescription,
	tbl_Skills.skillTitle,
	[tbl_SkillGroups-Skills].targetSkillLevel,
	[tbl_SkillGroups-Skills].skillGroupID

FROM
	tbl_Skills INNER JOIN
	[tbl_SkillGroups-Skills] ON tbl_Skills.skillID = [tbl_SkillGroups-Skills].skillID INNER JOIN
	[tbl_SkillGroups-Users] ON [tbl_SkillGroups-Skills].skillGroupID = [tbl_SkillGroups-Users].skillGroupID

WHERE
	([tbl_SkillGroups-Users].userID = @userID) AND 
	([tbl_SkillGroups-Users].customerID = @customerID) AND 
	([tbl_SkillGroups-Users].skillGroupArchived = 0)

ORDER BY
	tbl_Skills.skillTitle

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-23 : 06:09:04

Can you post your Table DDL, some sample data and the result that you want ?

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-06-23 : 07:14:11

Hi khtan
Hope this is okay:

http:/www.evildoctor.co.uk/tmp/myTables.sql
http:/www.evildoctor.co.uk/tmp/tbl_SkillGroups-Skills.csv
http:/www.evildoctor.co.uk/tmp/tblSkills.csv
http:/www.evildoctor.co.uk/tmp/tbl_skillgrouos-users.csv

The current result from the above search this resembles this (only last few results shown for brevity):


skillID skillDescription		skillTitle				skillTargetLevel  skillGroupID
5	NULL				Properties of Metals			100.00		  2
17	NULL				Safety Rules for Milling Machines	NULL		  4
1	The basics of where tools...	Tool Storage Locations			100.00		  1
1	The basics of where tools...	Tool Storage Locations			NULL		  5
18	NULL				Tools and Equipment			NULL		  4
16	NULL				Types of Milling Machines		NULL		  4

But I only want 1 occurrence of the skillID (shown "1" in the results)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-25 : 10:39:11

Sorry i can't access to the links at all. Can you just post it here instead.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-06-26 : 03:26:18

Database Tables

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tbl_LearningMaterial_tbl_Skills]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tbl_LearningMaterial] DROP CONSTRAINT FK_tbl_LearningMaterial_tbl_Skills
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_Skills]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_Skills]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_SkillGroups-Skills]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_SkillGroups-Skills]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_SkillGroups-Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbl_SkillGroups-Users]
GO

CREATE TABLE [dbo].[tbl_Skills] (
[customerID] [int] NOT NULL ,
[skillID] [int] IDENTITY (1, 1) NOT NULL ,
[skillTitle] [nvarchar] (100) COLLATE Latin1_General_CI_AS NOT NULL ,
[skillDescription] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[skillAimsObjectives] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
[skillOwnerID] [int] NULL ,
[skillOwnerName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[skillIsMandatory] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_SkillGroups-Skills] (
[customerID] [int] NOT NULL ,
[linkID] [int] IDENTITY (1, 1) NOT NULL ,
[skillGroupID] [int] NOT NULL ,
[skillID] [int] NOT NULL ,
[targetSkillLevel] [numeric](5, 2) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tbl_SkillGroups-Users] (
[linkID] [int] IDENTITY (1, 1) NOT NULL ,
[customerID] [int] NOT NULL ,
[userID] [int] NOT NULL ,
[skillGroupID] [int] NOT NULL ,
[dateOfAllocation] [datetime] NULL ,
[skillGroupArchived] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_Skills] WITH NOCHECK ADD
CONSTRAINT [DF_tbl_Skills_skillIsMandatory] DEFAULT (1) FOR [skillIsMandatory],
CONSTRAINT [PK_tbl_Skills] PRIMARY KEY CLUSTERED
(
[skillID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_SkillGroups-Skills] WITH NOCHECK ADD
CONSTRAINT [PK_tbl_SkillGroup-Skills] PRIMARY KEY CLUSTERED
(
[linkID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_SkillGroups-Users] WITH NOCHECK ADD
CONSTRAINT [DF_tbl_SkillGroups-Users_dateOfAllocation] DEFAULT (getdate()) FOR [dateOfAllocation],
CONSTRAINT [DF_tbl_SkillGroups-Users_skillGroupArchived] DEFAULT (0) FOR [skillGroupArchived],
CONSTRAINT [PK_tbl_SkillGroups-Users] PRIMARY KEY CLUSTERED
(
[linkID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tbl_Skills] ADD
CONSTRAINT [FK_tbl_Skills_tbl_Customers] FOREIGN KEY
(
[customerID]
) REFERENCES [dbo].[tbl_Customers] (
[CustomerID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[tbl_SkillGroups-Skills] ADD
CONSTRAINT [FK_tbl_SkillGroup-Skills_tbl_Customers] FOREIGN KEY
(
[customerID]
) REFERENCES [dbo].[tbl_Customers] (
[CustomerID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[tbl_SkillGroups-Users] ADD
CONSTRAINT [FK_tbl_SkillGroups-Users_tbl_Customers] FOREIGN KEY
(
[customerID]
) REFERENCES [dbo].[tbl_Customers] (
[CustomerID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO

Data for tbl_SkillGroups-Skills

customerID,linkID,skillGroupID,skillID,targetSkillLevel
2,1,1,1,100
2,2,1,3,75
2,3,1,4,50
2,4,2,5,100
2,5,2,6,75
2,6,2,7,50
2,7,2,8,100
2,8,2,9,100
2,9,3,10,100
2,10,3,11,50
2,11,3,12,50
2,12,3,13,50
2,13,3,14,80
2,14,3,15,100
2,15,4,16,
2,16,4,17
2,17,4,18
2,18,4,19
2,19,4,20
2,20,4,21
2,21,4,22
2,31,5,1

Data for tbl_SkillGroups-Users

linkID,customerID,userID,skillGroupID,dateOfAllocation,skillGroupArchived
1,2,3,1,15/05/2006 11:00,0
2,2,3,2,15/05/2006 11:00,0
3,2,3,3,15/05/2006 11:01,0
4,2,3,4,15/05/2006 11:01,0
5,2,4,1,15/05/2006 11:36,0
6,2,4,2,15/05/2006 11:36,0
7,2,3,5,22/06/2006 16:58,0

Data for tblSkills

customerID,skillID,skillTitle,skillDescription,skillAimsObjectives,skillOwnerID,skillOwnerName,skillIsMandatory
2,1,Tool Storage Locations,"The basics of where tools should be stored so that they can be easily located, but are also stored safely.",,,,1
2,3,Layout Design,,,,,1
2,4,Mechanical Drawing,,,,,1
2,5,Properties of Metals,,,,,1
2,6,Identification of Metals,,,,,1
2,7,Ferrous Metals,,,,,1
2,8,Nonferrous Metals,,,,,1
2,9,Heat Treatment of Metals,,,,,1
2,10,Portable Drill Use,,,,,1
2,11,Portable Grinder Use,,,,,1
2,13,Portable Sander and Polisher Use,,,,,1
2,14,Portable Metal Sawing Machine Use,,,,,1
2,15,Portable Coolant Attachment Use,,,,,1
2,16,Types of Milling Machines,,,,,1
2,17,Safety Rules for Milling Machines,,,,,1
2,18,Tools and Equipment,,,,,1
2,19,Indexing Fixture,,,,,1
2,20,Mounting and Indexing Work,,,,,1
2,21,General Milling Operations,,,,,1
2,22,Gear Cutting,,,,,1

T-SQL seemed so easy in the training videos...

cvraghu
Posting Yak Master

187 Posts

Posted - 2006-06-26 : 19:56:17

Since Skill 1 is part of two groups 1 and 5, you cannot say the rows are duplicates.

If you plan to have targetskilllevel & skillgroupid in the select list you cannot avoid duplicates.

To have only one row for each skill, remove the columns targetskilllevel & skill group id from the select list and use a Group By or distinct.

Review your requirements.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-06-27 : 03:34:05

Hi cvraghu,
Thanks for your post. The problem is that the query populates a dataTable in ASP.NET. ASP automatically assigns the skillID column in the results set as the primary key column. It then throws an error because 1 skill exists twice (once for each group as you pointed out).
I believed that it would be easier to modify the query in SQL than it would be to start modifying the dataTable properties in .NET, however, it seems I may be wrong given your explanation.
I will explore a solution in .net, although khtan if you have spotted anything that can be done, please feel free to let me know.

Thank you both again for your help.

T-SQL seemed so easy in the training videos...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources