Post reply

Returning data from linked tables

  • June 10, 2005 at 4:10 am

    #93198

    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 IDCompetencyCodeCountKeywords
    12K9
    24K2,K10,K12,K13
    31K11
    41K1,K11
    51''
    64K9
    70''

    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

    #564547

    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

    #564598

    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

    #564603

    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

    #564610

    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

    #564615

    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

    #564619

    try count(CompetencyCode_ID) instead of count(*).

  • June 10, 2005 at 8:36 am

    #564631

    ...ehm, yes should have thought of that

    Thanks for your help, you made my Friday afternoon

  • June 10, 2005 at 8:42 am

    #564632

    afternoon?? you luck bast@rd... it's 10H30 am here .

    HTH.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply