Joining two values together across multiple tables

[MGM]

This is a bit hard to explain but I'll try my best.

I have 2 tables. One table has a row with the following value:

2,3

The two numbers are both ID's that are in the second table. I need to write a query that will ultimately give me the values of the second table, merged together as follows:

Data One, Data Two

Instead of just the id's. Is this possible? I tried to figure out how to use a Split function in SQL but then I'd run into the problem of how to get both pieces of data once the ID's are split...

MGM out

[siva538]

You may probably consider using cursor for the first one and then retreive the data from second.

[Esteban404]

Similar to the cursor suggestion, I used a temp table to build the results then read them in. The final solution I had was to change the database design to use a join table to resolve the relationship into discreet records. (They can still come out as a comma del string for display purposes.) Once that was done, there were individual records for each value and are directly queriable without the mess in a sproc or other code.

Fortunately I *could* do that otherwise you're stuck with the mess.

_E

[ck9663]

This is a bit hard to explain but I'll try my best.

I have 2 tables. One table has a row with the following value:

2,3

The two numbers are both ID's that are in the second table. I need to write a query that will ultimately give me the values of the second table, merged together as follows:

Data One, Data Two

Instead of just the id's. Is this possible? I tried to figure out how to use a Split function in SQL but then I'd run into the problem of how to get both pieces of data once the ID's are split...

MGM out

Actually you can use your second table twice to relate the two keys. Just make sure to name the tables properly.

Paste this on your query analyzer:

set nocount on

declare @tbl1 table (col1 int, col2 int)
declare @tbl2 table (colkey int, coldesc varchar(20))

insert into @tbl1 (col1, col2) values (1,2)

insert into @tbl2 (colkey, coldesc) values (1, 'Data One')
insert into @tbl2 (colkey, coldesc) values (2, 'Data Two')
insert into @tbl2 (colkey, coldesc) values (3, 'Data Three')
insert into @tbl2 (colkey, coldesc) values (4, 'Data Four')
insert into @tbl2 (colkey, coldesc) values (5, 'Data Five')

select * from @tbl1

select * from @tbl2

select tbl1.col1, key1.coldesc, tbl1.col2, key2.coldesc
from @tbl1 tbl1 
	inner join @tbl2 key1 on key1.colkey = tbl1.col1
	inner join @tbl2 key2 on key2.colkey = tbl1.col2

Change the join from INNER to LEFT as necessary.

-- CK

[siva538]

Actually you can use your second table twice to relate the two keys. Just make sure to name the tables properly.

Paste this on your query analyzer:

set nocount on

declare @tbl1 table (col1 int, col2 int)
declare @tbl2 table (colkey int, coldesc varchar(20))

insert into @tbl1 (col1, col2) values (1,2)

insert into @tbl2 (colkey, coldesc) values (1, 'Data One')
insert into @tbl2 (colkey, coldesc) values (2, 'Data Two')
insert into @tbl2 (colkey, coldesc) values (3, 'Data Three')
insert into @tbl2 (colkey, coldesc) values (4, 'Data Four')
insert into @tbl2 (colkey, coldesc) values (5, 'Data Five')

select * from @tbl1

select * from @tbl2

select tbl1.col1, key1.coldesc, tbl1.col2, key2.coldesc
from @tbl1 tbl1 
	inner join @tbl2 key1 on key1.colkey = tbl1.col1
	inner join @tbl2 key2 on key2.colkey = tbl1.col2

Change the join from INNER to LEFT as necessary.

-- CK

This is not a scalable though ! If there are more columns, it won't work.

[ck9663]

Option 1:
1. Add more join, just make sure you alias each use of your table2.

Option 2:
1. Unpivot your table
2. Use a single join to get the data from table2. You will have an output that looks like:

COL, DESCRIPTION
1       Data One
2       Data Two

3. Pivot your table back. To get the desired

 COL1, DESCRIPTION1, COL2, DESCRIPTION2
   1       Data One             2       Data Two

No temp table necessary.

-- CK

[siva538]

Yes, second option is better one. But understand that there are code changes involved for more number of columns, where as if you go for a cursor your life becomes easier and using @@FETCH_STATUS you can identify n number of records and their corresponding values from table2.

HTH...

[ck9663]

Yes. There's always cursor.

I made the suggestion based on your example. Your sample data have two columns which I think JOIN would be sufficient enough. If you're talking about a lot of columns, it'll be your call.

I just use cursor as last resort.

-- CK

[MGM]

I actually ended up changing my table structure around so no need for all of the above. Thanks for the help however, it was a very interesting read to see what solutions you guys could come up with.

MGM out