By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

Joining two values together across multiple tables

P: 19
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
Apr 16 '08 #1
Share this Question
Share on Google+
8 Replies


P: 44
You may probably consider using cursor for the first one and then retreive the data from second.
Apr 16 '08 #2

P: 4
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
Apr 16 '08 #3

ck9663
Expert 2.5K+
P: 2,878
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:

Expand|Select|Wrap|Line Numbers
  1. set nocount on
  2.  
  3. declare @tbl1 table (col1 int, col2 int)
  4. declare @tbl2 table (colkey int, coldesc varchar(20))
  5.  
  6. insert into @tbl1 (col1, col2) values (1,2)
  7.  
  8. insert into @tbl2 (colkey, coldesc) values (1, 'Data One')
  9. insert into @tbl2 (colkey, coldesc) values (2, 'Data Two')
  10. insert into @tbl2 (colkey, coldesc) values (3, 'Data Three')
  11. insert into @tbl2 (colkey, coldesc) values (4, 'Data Four')
  12. insert into @tbl2 (colkey, coldesc) values (5, 'Data Five')
  13.  
  14. select * from @tbl1
  15.  
  16. select * from @tbl2
  17.  
  18. select tbl1.col1, key1.coldesc, tbl1.col2, key2.coldesc
  19. from @tbl1 tbl1 
  20.     inner join @tbl2 key1 on key1.colkey = tbl1.col1
  21.     inner join @tbl2 key2 on key2.colkey = tbl1.col2
  22.  
  23.  
Change the join from INNER to LEFT as necessary.

-- CK
Apr 16 '08 #4

P: 44
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:

Expand|Select|Wrap|Line Numbers
  1. set nocount on
  2.  
  3. declare @tbl1 table (col1 int, col2 int)
  4. declare @tbl2 table (colkey int, coldesc varchar(20))
  5.  
  6. insert into @tbl1 (col1, col2) values (1,2)
  7.  
  8. insert into @tbl2 (colkey, coldesc) values (1, 'Data One')
  9. insert into @tbl2 (colkey, coldesc) values (2, 'Data Two')
  10. insert into @tbl2 (colkey, coldesc) values (3, 'Data Three')
  11. insert into @tbl2 (colkey, coldesc) values (4, 'Data Four')
  12. insert into @tbl2 (colkey, coldesc) values (5, 'Data Five')
  13.  
  14. select * from @tbl1
  15.  
  16. select * from @tbl2
  17.  
  18. select tbl1.col1, key1.coldesc, tbl1.col2, key2.coldesc
  19. from @tbl1 tbl1 
  20.     inner join @tbl2 key1 on key1.colkey = tbl1.col1
  21.     inner join @tbl2 key2 on key2.colkey = tbl1.col2
  22.  
  23.  
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.
Apr 17 '08 #5

ck9663
Expert 2.5K+
P: 2,878
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:
Expand|Select|Wrap|Line Numbers
  1. COL, DESCRIPTION
  2. 1       Data One
  3. 2       Data Two
  4.  
3. Pivot your table back. To get the desired
Expand|Select|Wrap|Line Numbers
  1.  COL1, DESCRIPTION1, COL2, DESCRIPTION2
  2.    1       Data One             2       Data Two
No temp table necessary.

-- CK
Apr 17 '08 #6

P: 44
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...
Apr 17 '08 #7

ck9663
Expert 2.5K+
P: 2,878
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
Apr 17 '08 #8

P: 19
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
Apr 18 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.