SQL - Joining tables where one of the columns is a list

Question

Hi Experts,

I'm tryin to join two tables. The problem i'm having is that one of the columns i'm trying to join on is a list.

So is it possible to join two tables using "IN" rather than "=". Along the lines of

SELECT ID  
FROM tableA INNER JOIN  
tableB ON tableB.misc IN tableA.misc  
WHERE tableB.miscTitle = 'help me please'  


tableB.misc = 1  
tableA.misc = 1,2,3  

Thanks in advance

Answer 1

Try this:

SELECT ID  
FROM tableA INNER JOIN  
tableB ON ',' + TableA.misc + ',' like '%,' + cast(tableB.misc as varchar) + ',%'
WHERE tableB.miscTitle = 'help me please'  

Answer 2

Is ID also in tableB? If so, you can reverse the tables, and run the IN backwards, in the WHERE section, like so:

SELECT ID
FROM tableB
WHERE tableB.miscTitle = 'help me please'
    AND tableB.misc IN (SELECT tableA.misc FROM tableA)

If it's not, you could use a cross join to get all combinations of rows between the tables, then remove the rows that don't obey the IN. WARNING: This will become a huge join if the tables are large. Example:

SELECT ID
FROM tableA
CROSS JOIN tableB
WHERE tableB.miscTitle = 'help me please'
    AND tableB.misc IN tableA.misc

EDIT: didn't realize "in a list" meant a comma-delimited VARCHAR. SQL's IN won't work for that, nor should you ever store joinable data that way in a database.

Answer 3

No what you want is not possible without a major workaround. DO NOT STORE ITEMS YOU WANT TO JOIN TO IN A LIST! In fact a comma delimited list should almost never be stored in a database. It is only acceptable if this is note type information that will never need to be used in a query where clasue or join.

If you are stuck with this horrible design, then you will have to parse out the list to a temp table or table variable and then join through that.

Answer 4

A string parsing function like the one found here together with a CROSS APPLY should do the trick.

CREATE FUNCTION [dbo].[fnParseStringTSQL] (@string NVARCHAR(MAX),@separator NCHAR(1))
RETURNS @parsedString TABLE (string NVARCHAR(MAX))
AS 
BEGIN
   DECLARE @position int
   SET @position = 1
   SET @string = @string + @separator
   WHILE charindex(@separator,@string,@position) <> 0
      BEGIN
         INSERT into @parsedString
         SELECT substring(@string, @position, charindex(@separator,@string,@position) - @position)
         SET @position = charindex(@separator,@string,@position) + 1
      END
     RETURN
END
go

declare @tableA table (
    id int,
    misc char(1)
)

declare @tableB table (
    misc varchar(10),
    miscTitle varchar(20)
)

insert into @tableA
    (id, misc)
    values
    (1, '1')

insert into @tableB
    (misc, miscTitle)
    values
    ('1,2,3','help me please')

select id
    from @tableB b
        cross apply dbo.fnParseStringTSQL(b.misc,',') p
        inner join @tableA a
            on a.misc = p.string
    where b.miscTitle = 'help me please'

drop function dbo.fnParseStringTSQL