Joining Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Joining Tables

Author

Topic

Friznost
Starting Member

20 Posts

Posted - 2007-08-03 : 14:17:28

I have two tables X and Y which need to join together. Table X has an ID field which connects to an ID field in Table Y. My problem is the ID field in Table X can contain multiple ID's EX:

Table X
ID
-------
2,1,4,
2,5,
1,
3,1,2,4,
ect...

whereas the ID field in Table Y contains one ID in each row EX:

Table Y
ID
-------
1
2
3
4
5
ect...

Is there a way to join these two tables together?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-03 : 14:25:31

SELECT *
FROM TableY AS y
INNER JOIN TableX AS x ON ',' + x.ID LIKE '%,' + y.ID + ',%'

E 12°55'05.25"
N 56°04'39.16"

Friznost
Starting Member

20 Posts

Posted - 2007-08-03 : 15:05:24

This won't work. The first row would be ',2,1,4,' Like '%1%'. It would never find a match.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-03 : 15:23:00

quote:
Originally posted by Friznost

This won't work. The first row would be ',2,1,4,' Like '%1%'. It would never find a match.

Have you tried, or are you just guessing?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-03 : 15:31:01

[code]-- Prepare sample data
DECLARE @TableX TABLE (ID VARCHAR(20))

INSERT @TableX
SELECT '2,1,4,' UNION ALL
SELECT '2,5,' UNION ALL
SELECT '1,' UNION ALL
SELECT '3,1,2,4,'

DECLARE @TableY TABLE (ID VARCHAR(4))

INSERT @TableY
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '4' UNION ALL
SELECT '5'

-- Show the expected output
SELECT y.ID AS yID,
x.ID AS xID
FROM @TableY AS y
INNER JOIN @TableX AS x ON ',' + x.ID LIKE '%,' + y.ID + ',%'[/code]Output from above code is[code]yID xID
--- --------
1 2,1,4,
2 2,1,4,
4 2,1,4,
2 2,5,
5 2,5,
1 1,
1 3,1,2,4,
2 3,1,2,4,
3 3,1,2,4,
4 3,1,2,4,[/code]If this is not what you want, you better explain yourself more clearly!
Or at least TRY the suggestions given to you...

E 12°55'05.25"
N 56°04'39.16"

Friznost
Starting Member

20 Posts

Posted - 2007-08-03 : 16:27:47

What I'm looking for is....

Table X
ID
-------
2,1,4,
2,5,
1,
3,1,2,4,
ect...

Table Y
ID Color
------- -------
1 Green
2 Blue
3 Red
4 Yellow
5 Orange
ect...

I need my output to be...

ID Color
------- -------
2,1,4, Blue,Green,Yellow
2,5, Blue, Orange
1, Gren
3,1,2,4, Red, Green, Blue, Yellow
ect...

Sorry for the confusion and thanks for the help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-03 : 16:29:40

Why didn't you tell us from the beginning? What are you afraid of?

E 12°55'05.25"
N 56°04'39.16"

Friznost
Starting Member

20 Posts

Posted - 2007-08-07 : 11:50:36

Below is the full answer to this problem...

Create Table #Color(ColorID Varchar(5), ColorDescription Varchar(100))
INSERT INTO #Color
SELECT '1', 'Blue'
UNION ALL
SELECT '2', 'Red'
UNION ALL
SELECT '3', 'Green'
UNION ALL
SELECT '4', 'Yellow'
UNION ALL
SELECT '5', 'Black'

Create Table #Test(TestID Varchar(100))
INSERT INTO #Test
SELECT '1,2,'
UNION ALL
SELECT '3,5,4,1,'
UNION ALL
SELECT '4,'

SELECT x.TestID, Y.ColorDescription
INTO #Test2
FROM #Test x
INNER JOIN
#Color Y ON (',' + x.TestID) LIKE ('%,' + y.ColorID + ',%')

DECLARE @tbl TABLE (id INT PRIMARY KEY, list VARCHAR(8000))
SET NOCOUNT ON
DECLARE @c INT, @p VARCHAR(8000), @cNext INT, @pNext VARCHAR(100)
DECLARE c CURSOR FOR
SELECT Convert(Int,Replace(TestId, ',', '')), ColorDescription
FROM #Test2
ORDER BY Convert(Int,Replace(TestId, ',', '')), ColorDescription
OPEN c
FETCH NEXT FROM c INTO @cNext, @pNext
SET @c = @cNext
WHILE @@FETCH_STATUS = 0
BEGIN
IF @cNext > @c BEGIN
INSERT @tbl SELECT @c, @p
SELECT @p = @PNext, @c = @cNext
END ELSE
SET @p = COALESCE(@p + ',', SPACE(0)) + @pNext
FETCH NEXT FROM c INTO @cNext, @pNext
END
INSERT @tbl SELECT @c, @p
CLOSE c
DEALLOCATE c
SELECT * FROM @tbl;

DROP TABLE #Test
DROP TABLE #Color
DROP TABLE #Test2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources