Returning data from linked tables
-
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
-
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.
-
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
-
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
-
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.
-
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
-
try count(CompetencyCode_ID) instead of count(*).
-
...ehm, yes should have thought of that
Thanks for your help, you made my Friday afternoon
-
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