Advanced Join?

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 Color
- -
1 Green
2 Blue
3 Red
4 Yellow
5 Orange
ect...

Is there a way to join these two tables together? I need the output to be...

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

Yes, but it will be messy and inefficient. And most likely, it will ignore any indexing.

You have violated a cardinal rule with ID columns. In order to efficiently link tables, they must be individual values -NOT simulated arrays of values. This is really is a bad design that will continue to haunt you throughout the project lifecycle.

SELECT

x.ID,

y.Color

FROM Table_X x

JOIN Table_Y x

ON ( ',' + x.ID ) LIKE ( ',' + y.ID + ',' )

There are some other methods as well, one of which would create a #Temp table, split apart the separate ID's in Table_X, and then JOIN that #Temp table with these two tables. Here is a link to that approach. And another.

You really 'shouldn't do that kind of 'trickery' with key values. The continual hassle with the data just isn't worth the 'buzz' gained from being so clever...

It's NOT an advanced JOIN, it is a very, very silly table design!

|||If you think this is "silly" you should see the rest of the database! The sad part about this whole thing is that our company purchased this application from a leading medical management software company for 1,$$$,$$$. Your example doesn't give me the output I was looking for but I will try the example from the link you provided on Monday when I get back to work. Thanks for your help.
|||

I see that I left out an important piece of the code. My apologies.

SELECT

x.ID,

y.Color

FROM Table_X x

JOIN Table_Y x

ON ( ',' + x.ID ) LIKE ( '%,' + y.ID + ',%' )

That 'should' work a bit better.

I understand. Sometimes I just don't get how seemingly intelligent folks spend so much money for such poorly designed software. But it happens every day. And then we have to support it.

|||

Your query gives me....

Table_X.ID Table_Y.Color

2,1,4, Blue
2,1,4, Green
2,1,4, Yellow
2,5, Blue
2,5, Orange
1, Green
3,1,2,4, Red
3,1,2,4, Green
3,1,2,4, Blue
3,1,2,4, Yellow

How do you get it to.?.?.?.

Table_X.ID Table_Y.Color
-
2,1,4, Blue,Green,Yellow
2,5, Blue, Orange
1, Green
3,1,2,4, Red, Green, Blue, Yellow

|||

What you are now requesting is a function for the display tool. The client application 'should' be creating a string array to put those values together. Folks often forget, SQL Server excels at storing and retrieving data, not at creating displays. But so many application are being created by folks that really aren't very good programmers at all, so things like this get stuffed into SQL Server.

You can use one of the methods listed in these resources.

Lists -Field Concatenation, One Field to Itself for string
SQL 2000 http://omnibuzz-sql.blogspot.com/2006/06/concatenate-values-in-column-in-sql.html
SQL 2005 http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx
http://www.projectdmx.com/tsql/rowconcatenate.aspx

|||

Thanks for all your help!!!