June 10, 2005 at 4:10 am
Hi,
I have a problem that revolves around returning data from linked tables which are used to represent many-to-many relationships, and I would like to try and resolve it without using cursors. I will first of all include what I would like to see returned in a dataset:
Course ID | CompetencyCodeCount | Keywords |
1 | 2 | K9 |
2 | 4 | K2,K10,K12,K13 |
3 | 1 | K11 |
4 | 1 | K1,K11 |
5 | 1 | '' |
6 | 4 | K9 |
7 | 0 | '' |
I have tried resolving using temporary tables, but have only managed to bring my brain close to melt-down. ANY help would be greatly appreciated.
Below are the DDL and data scripts for the required tables.
Thanks
/* FORGIVE THE BIZARRE COLLATION USED, BUT OUR CLIENT REQUESTED IT !!! */
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[XCourse]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[XCourse]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[XCourseKeyword]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[XCourseKeyword]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[XCourse_CompetencyCode]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[XCourse_CompetencyCode]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[XCourse_CourseKeyword]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[XCourse_CourseKeyword]
GO
CREATE TABLE [dbo].[XCourse] (
[Course_ID] [int] IDENTITY (1, 1) NOT NULL ,
[CourseCode] [varchar] (10) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL ,
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[XCourseKeyword] (
[CourseKeyword_ID] [int] IDENTITY (1, 1) NOT NULL ,
[CourseKeywordCode] [varchar] (10) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL ,
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[XCourse_CompetencyCode] (
[Course_ID] [int] NOT NULL ,
[CompetencyCode_ID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[XCourse_CourseKeyword] (
[Course_ID] [int] NOT NULL ,
[CourseKeyword_ID] [int] NOT NULL
) ON [PRIMARY]
GO
/* STATIC DATA */
TRUNCATE TABLE [dbo].[XCourseKeyword]
SET IDENTITY_INSERT [dbo].[XCourseKeyword] ON
INSERT INTO [dbo].[XCourseKeyword] (CourseKeyword_ID, CourseKeyWordCode) VALUES (1, 'K1')
INSERT INTO [dbo].[XCourseKeyword] (CourseKeyword_ID, CourseKeyWordCode) VALUES (2, 'K2')
INSERT INTO [dbo].[XCourseKeyword] (CourseKeyword_ID, CourseKeyWordCode) VALUES (3, 'K3')
INSERT INTO [dbo].[XCourseKeyword] (CourseKeyword_ID, CourseKeyWordCode) VALUES (4, 'K4')
INSERT INTO [dbo].[XCourseKeyword] (CourseKeyword_ID, CourseKeyWordCode) VALUES (5, 'K5')
INSERT INTO [dbo].[XCourseKeyword] (CourseKeyword_ID, CourseKeyWordCode) VALUES (6, 'K6')
INSERT INTO [dbo].[XCourseKeyword] (CourseKeyword_ID, CourseKeyWordCode) VALUES (7, 'K7')
INSERT INTO [dbo].[XCourseKeyword] (CourseKeyword_ID, CourseKeyWordCode) VALUES (8, 'K8')
INSERT INTO [dbo].[XCourseKeyword] (CourseKeyword_ID, CourseKeyWordCode) VALUES (9, 'K9')
INSERT INTO [dbo].[XCourseKeyword] (CourseKeyword_ID, CourseKeyWordCode) VALUES (10, 'K10')
INSERT INTO [dbo].[XCourseKeyword] (CourseKeyword_ID, CourseKeyWordCode) VALUES (11, 'K11')
INSERT INTO [dbo].[XCourseKeyword] (CourseKeyword_ID, CourseKeyWordCode) VALUES (12, 'K12')
INSERT INTO [dbo].[XCourseKeyword] (CourseKeyword_ID, CourseKeyWordCode) VALUES (13, 'K13')
SET IDENTITY_INSERT [dbo].[XCourseKeyword] OFF
GO
TRUNCATE TABLE [dbo].[XCourse]
SET IDENTITY_INSERT [dbo].[XCourse] ON
INSERT INTO [dbo].[XCourse] ([Course_ID], CourseCode) VALUES (1, 'Course1')
INSERT INTO [dbo].[XCourse] ([Course_ID], CourseCode) VALUES (2, 'Course2')
INSERT INTO [dbo].[XCourse] ([Course_ID], CourseCode) VALUES (3, 'Course3')
INSERT INTO [dbo].[XCourse] ([Course_ID], CourseCode) VALUES (4, 'Course4')
INSERT INTO [dbo].[XCourse] ([Course_ID], CourseCode) VALUES (5, 'Course5')
INSERT INTO [dbo].[XCourse] ([Course_ID], CourseCode) VALUES (6, 'Course6')
INSERT INTO [dbo].[XCourse] ([Course_ID], CourseCode) VALUES (7, 'Course7')
SET IDENTITY_INSERT [dbo].[XCourse] OFF
GO
TRUNCATE TABLE [dbo].[XCourse_CourseKeyword]
INSERT INTO [dbo].[XCourse_CourseKeyword] ([Course_ID], CourseKeyword_ID) VALUES (1, 9)
INSERT INTO [dbo].[XCourse_CourseKeyword] ([Course_ID], CourseKeyword_ID) VALUES (2, 2)
INSERT INTO [dbo].[XCourse_CourseKeyword] ([Course_ID], CourseKeyword_ID) VALUES (2,10)
INSERT INTO [dbo].[XCourse_CourseKeyword] ([Course_ID], CourseKeyword_ID) VALUES (2,12)
INSERT INTO [dbo].[XCourse_CourseKeyword] ([Course_ID], CourseKeyword_ID) VALUES (2,13)
INSERT INTO [dbo].[XCourse_CourseKeyword] ([Course_ID], CourseKeyword_ID) VALUES (3,11)
INSERT INTO [dbo].[XCourse_CourseKeyword] ([Course_ID], CourseKeyword_ID) VALUES (4, 1)
INSERT INTO [dbo].[XCourse_CourseKeyword] ([Course_ID], CourseKeyword_ID) VALUES (4,11)
INSERT INTO [dbo].[XCourse_CourseKeyword] ([Course_ID], CourseKeyword_ID) VALUES (6, 9)
GO
TRUNCATE TABLE [dbo].[XCourse_CompetencyCode]
INSERT INTO [dbo].[XCourse_CompetencyCode] ([Course_ID], CompetencyCode_ID) VALUES (1, 79)
INSERT INTO [dbo].[XCourse_CompetencyCode] ([Course_ID], CompetencyCode_ID) VALUES (1,124)
INSERT INTO [dbo].[XCourse_CompetencyCode] ([Course_ID], CompetencyCode_ID) VALUES (2, 77)
INSERT INTO [dbo].[XCourse_CompetencyCode] ([Course_ID], CompetencyCode_ID) VALUES (2,119)
INSERT INTO [dbo].[XCourse_CompetencyCode] ([Course_ID], CompetencyCode_ID) VALUES (2,121)
INSERT INTO [dbo].[XCourse_CompetencyCode] ([Course_ID], CompetencyCode_ID) VALUES (2,123)
INSERT INTO [dbo].[XCourse_CompetencyCode] ([Course_ID], CompetencyCode_ID) VALUES (3,201)
INSERT INTO [dbo].[XCourse_CompetencyCode] ([Course_ID], CompetencyCode_ID) VALUES (4,201)
INSERT INTO [dbo].[XCourse_CompetencyCode] ([Course_ID], CompetencyCode_ID) VALUES (5,200)
INSERT INTO [dbo].[XCourse_CompetencyCode] ([Course_ID], CompetencyCode_ID) VALUES (6, 79)
INSERT INTO [dbo].[XCourse_CompetencyCode] ([Course_ID], CompetencyCode_ID) VALUES (6,124)
INSERT INTO [dbo].[XCourse_CompetencyCode] ([Course_ID], CompetencyCode_ID) VALUES (6,125)
INSERT INTO [dbo].[XCourse_CompetencyCode] ([Course_ID], CompetencyCode_ID) VALUES (6,126)
GO
June 10, 2005 at 6:24 am
You'll need something like this :
IF Object_id('ListTableColumns') > 0
DROP FUNCTION ListTableColumns
GO
CREATE FUNCTION dbo.ListTableColumns (@TableID as int)
RETURNS varchar(8000)
AS
BEGIN
Declare @Items as varchar(8000)
SET @Items = ''
SELECT
@Items = @Items + C.Name + ', '
FROMdbo.SysColumns C
WHEREC.id = @TableID
AND OBJECTPROPERTY(@TableID, 'IsTable') = 1
ORDER BYC.Name
SET @Items = LEFT(@Items, ABS(DATALENGTH(@Items) - 2))
RETURN @Items
END
GO
Select dbo.ListTableColumns(Object_id('SysObjects'))
--base_schema_ver, cache, category, crdate, deltrig, ftcatid, id, indexdel, info, instrig, name, parent_obj, refdate, replinfo, schema_ver, seltrig, stats_schema_ver, status, sysstat, type, uid, updtrig, userstat, version, xtype
DROP FUNCTION ListTableColumns
Just drop a line if you need help adapting it to your needs.
June 10, 2005 at 7:53 am
Hi Remi,
sorry but I'm not sure I understand how to use the ListTableColumns function? I want to group aggregate values for each CourseID in the Course table. So how do I adapt the function to do that?
Thanks for your help
June 10, 2005 at 7:58 am
Try this modified select :
Select O.Name, O.id, count(*) as Total, dbo.ListTableColumns(O.id) from dbo.SysObjects O inner join dbo.SysColumns C on O.id = C.id group by O.ID, O.Name
June 10, 2005 at 8:03 am
BTW, the function is meant to only generate the items list. It cannot be modified to return the quantity (even if it could count 'em easily). That's why I added the group by in the 2nd version. I'm not sure you'll need an inner join though.
June 10, 2005 at 8:13 am
Wow, that is pretty neat
One small problem though regards the fact that nothing is returned for CourseID = 7. So I modified the joins to left joins, and on the count column course_id returns a value of 1 whereas it should be zero. Any way around that?
IF Object_id('ListTableColumns') > 0
DROP FUNCTION ListTableColumns
GO
CREATE FUNCTION dbo.ListTableColumns (@TableID as int)
RETURNS varchar(8000)
AS
BEGIN
Declare @Items as varchar(8000)
SET @Items = ''
SELECT
@Items = @Items + D.[Name] + ', '
FROM dbo.Course_CourseKeyword C
LEFT JOIN dbo.CourseKeyword D ON C.CourseKeyword_ID = D.CourseKeyword_ID
WHERE C.Course_id = @TableID
AND OBJECTPROPERTY(@TableID, 'IsTable') = 1
ORDER BY C.CourseKeyword_ID
SET @Items = LEFT(@Items, ABS(DATALENGTH(@Items) - 2))
RETURN @Items
END
GO
Select O.Course_id, count(*) as Total, dbo.ListTableColumns(O.Course_id) from dbo.Course O LEFT join dbo.Course_CompetencyCode C on O.Course_id = C.Course_id group by O.Course_id
DROP FUNCTION ListTableColumns
Thanks again
June 10, 2005 at 8:17 am
try count(CompetencyCode_ID) instead of count(*).
June 10, 2005 at 8:36 am
...ehm, yes should have thought of that
Thanks for your help, you made my Friday afternoon
June 10, 2005 at 8:42 am
afternoon?? you luck bast@rd... it's 10H30 am here .
HTH.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy