Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    32

    Question Unanswered: Retrieve a recordset from one table based on the CSV values in the field of another

    Hello!

    I have 2 tables, one that contains a set of codes and their definitions, and another where each record has a field that contains several of these codes separated by commas:

    Tab1

    SubCode | Definition
    ---------------
    S100 | Def of S100
    S101 | Def of S101
    S102 | Def of S102

    Tab2

    DepID | Purpcode |SubCodes
    -----------------------------
    1 | P100 | S100,S101,S102
    1 | P101 | S100, S101
    2 | P101 | S100,S102

    I'm trying to create a query against Tab1 so that it retrieves a recordset of Subcodes and definitions based on the contents of the Subcodes field for a record in Tab2. I've tried this using a subquery, as follows:

    SELECT SubCode ,Definition
    FROM Tab1
    WHERE SubjectCode IN
    (SELECT CHAR(39) + REPLACE(SubjectCodes, CHAR(44), CHAR(39 + CHAR(44)+ CHAR(39)) + CHAR(39)
    FROM Tab2
    WHERE DepID = 1 AND PurposeCode = 'P101')

    The subquery will return: 'S100','S101' and I expect the final recordset to be:

    SubCode | Definition
    ---------------
    S100 | Def of S100
    S101 | Def of S101

    However, it's not returning any records. If I execute the subquery separately and then plug its results into the main query e.g.

    SELECT SubCode ,Definition
    FROM Tab1
    WHERE SubjectCode IN ('S100','S101')

    it returns the expected recordset. Does anyone have any pointers? It's driving me nuts..

    Cheers
    Greg

    Complete DDL, Sample Data, and Query below:

    Code:
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SubjectCodeDefinition]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[SubjectCodeDefinition]
    GO
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DepartmentReturn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[DepartmentReturn]
    GO
    
    CREATE TABLE [dbo].[SubjectCodeDefinition] (
    	[SubjectCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Definition] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
    ) ON [PRIMARY]
    GO
    
    CREATE TABLE [dbo].[DepartmentReturn] (
    	[DeptID] [int] NULL ,
    	[PurposeCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[SubjectCodes] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    ) ON [PRIMARY]
    GO
    
    INSERT INTO SubjectCodeDefinition(SubjectCode, Definition)
    SELECT 'S100', 'Definition of Code S100' UNION ALL
    SELECT 'S101', 'Definition of Code S101' UNION ALL
    SELECT 'S102', 'Definition of Code S102' UNION ALL
    SELECT 'S103', 'Definition of Code S103' UNION ALL
    SELECT 'S104', 'Definition of Code S104' UNION ALL
    SELECT 'S105', 'Definition of Code S105'
    GO
    
    INSERT INTO DepartmentReturn(DeptID,PurposeCode,SubjectCodes)
    SELECT 1,'P100','S100,S101,S104' UNION ALL
    SELECT 1,'P101','S102,S103' UNION ALL
    SELECT 1,'P102','S100,S101,S105' UNION ALL
    SELECT 2,'P100','S100,S101,S104,S105' UNION ALL
    SELECT 2,'P103','S103,S104,S105' UNION ALL
    SELECT 3,'P100','S100,S102,S104'
    GO
    
    SELECT    SubjectCode ,Definition
    FROM      SubjectCodeDefinition
    WHERE     SubjectCode IN 
              (SELECT     CHAR(39) + REPLACE(SubjectCodes, CHAR(44), CHAR(39)+ CHAR(44)+ CHAR(39)) + CHAR(39)
               FROM DepartmentReturn
               WHERE DeptID = 1 AND PurposeCode = 'P102')

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    First normal form worth a look http://r937.com/relational.html
    and this will cause a scan.

    Code:
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SubjectCodeDefinition]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[SubjectCodeDefinition]
    GO
     
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DepartmentReturn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[DepartmentReturn]
    GO
     
    CREATE TABLE [dbo].[SubjectCodeDefinition] (
     [SubjectCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [Definition] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
    ) ON [PRIMARY]
    GO
     
    CREATE TABLE [dbo].[DepartmentReturn] (
     [DeptID] [int] NULL ,
     [PurposeCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [SubjectCodes] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    ) ON [PRIMARY]
    GO
     
    INSERT INTO SubjectCodeDefinition(SubjectCode, Definition)
    SELECT 'S100', 'Definition of Code S100' UNION ALL
    SELECT 'S101', 'Definition of Code S101' UNION ALL
    SELECT 'S102', 'Definition of Code S102' UNION ALL
    SELECT 'S103', 'Definition of Code S103' UNION ALL
    SELECT 'S104', 'Definition of Code S104' UNION ALL
    SELECT 'S105', 'Definition of Code S105'
    GO
     
    INSERT INTO DepartmentReturn(DeptID,PurposeCode,SubjectCodes)
    SELECT 1,'P100','S100,S101,S104' UNION ALL
    SELECT 1,'P101','S102,S103' UNION ALL
    SELECT 1,'P102','S100,S101,S105' UNION ALL
    SELECT 2,'P100','S100,S101,S104,S105' UNION ALL
    SELECT 2,'P103','S103,S104,S105' UNION ALL
    SELECT 3,'P100','S100,S102,S104'
    GO
     
    SELECT  SubjectCode ,Definition
    FROM SubjectCodeDefinition
       INNER JOIN
      DepartmentReturn ON
        ',' + DepartmentReturn.SubjectCodes + ',' LIKE '%,' + SubjectCodeDefinition.SubjectCode + ',%'
    WHERE DeptID = 1 AND PurposeCode = 'P102'
     
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SubjectCodeDefinition]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[SubjectCodeDefinition]
    GO
     
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DepartmentReturn]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[DepartmentReturn]
    GO
    Dynamic SQL is another alternative. Changing the design would be my preference.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •