SQL - Joining tables where one of the columns is a list
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
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.
Try this:
SELECT ID
FROM tableA INNER JOIN
tableB ON ',' + TableA.misc + ',' like '%,' + cast(tableB.misc as varchar) + ',%'
WHERE tableB.miscTitle = 'help me please'
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
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.
精彩评论