Solved

T-SQL: joining tables when the referencing key in one of the tables is in three columns instead of one.

Posted on 2013-06-07
11
495 Views
Last Modified: 2013-07-05
Table2 has three columns with the values that reference the same parent column1 in table1 . Either one of the columns has the value, or two, or all three, otherwise the value is empty string. That's how we can join those two tables using the value that exists in one of three columns and the value in the table2 column1.
I have to create additional column in the table2 and populate it with the matching value from the column2 of the parent table1.
Any ideas about the best way to accomplish the task?
0
Comment
Question by:quasar_ee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 39230853
Can you post some sample data with expected result?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39230904
is this what you are describing?
select
  table1.column1
, table2.option1 -- null, or, = table1.column1
, table2.option2 -- null, or, = table1.column1
, table2.option3 -- null, or, = table1.column1
from table1
inner join table2 
   ON table1.column1 = coalesce(table2.option1,table2.option2,table2.option3)

Open in new window

can all three 'option' fields be null, or must at least one of them have a value?
0
 
LVL 4

Accepted Solution

by:
BAKADY earned 500 total points
ID: 39234171
T-SQL 101

parenttablechildtableresult
select P.*, C.*
from ParentTable P left join ChildTable C
  on P.ParentID in (C.ParentID_1, C.ParentID_2, C.ParentID_3)

Open in new window

coalesce is wrong... you can try it with something like this:

select coalesce(3, 2, 1) as test  <<< this returns 3
GO
select coalesce(1, 2, 3) as test  <<< this returns 1
GO

if you are searching for 2, you will never find it!

regards
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 49

Expert Comment

by:PortletPaul
ID: 39234181
coalesce is not 'wrong'
>>Table2 has three columns with the values that reference the same parent column1
>>Either one of the columns has the value, or two, or all three

coalesce will simply locate the first non-null value, then join on that...
i.e. not "wrong"

select coalesce(null, 2, null) as test  <<< this will return 2
select coalesce(null, 2, 3) as test  <<< this will return 2
select coalesce(1, 2, 3) as test  <<< this will return 1
etc.

then the join is only performed on one value too
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39234200
PortletPaul: you didn't understand my post...

select coalesce(1, 2, 3) as test  <<< this returns 1
GO

if you are searching for the value "2", you will never find it!

 quasar_ee never said that just the first not null value is right, maybe he want to catch all of them
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39234204
yes, but that's because you have non-null values in all columns...
a. they may be null
b. if not null, they are the same values

select coalesce(1,1,1) as test << returns 1
select coalesce(null,1,1) as test << returns 1
select coalesce(null,null,1) as test << returns 1

the test you propose is invalid for the given conditions

also, see: http://sqlfiddle.com/#!3/ac327/3

The purpose of my original suggestion is actually (although I didn't state it):
"Why add another column?"
"You already have 3, adding a fourth does not seem (to me) to make sense"

[Mind you not sure why there are 3 in the first place.]
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39234212
quasar_ee: use left join to get all ParentTable's records and their ChildTable's matches
select P.*, C.*
from ParentTable P left join ChildTable C
  on P.ParentID in (C.ParentID_1, C.ParentID_2, C.ParentID_3)

Open in new window

or simplified inner join to get just matches between both tables...
select P.*, C.*
from ParentTable P, ChildTable C
where P.ParentID in (C.ParentID_1, C.ParentID_2, C.ParentID_3)

Open in new window

0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39234237
Here, read again:
Table2 has three columns with the values that reference the same parent column1 in table1 . Either one of the columns has the value, or two, or all three, otherwise the value is empty string. That's how we can join those two tables using the value that exists in one of three columns and the value in the table2 column1.
I have to create additional column in the table2 and populate it with the matching value from the column2 of the parent table1.
Any ideas about the best way to accomplish the task?

you see that:
... otherwise the value is empty string ...

quasar_ee didn't mentioned that the field's value is null, otherwise it is a empty string... here you have to consider for char datatype with default value space and blah, blah, blah....
but not NULL... sorry not NULL no COALESCE...

we can talk over, but without some examples from quasar_ee we are both wrong...

differences between COALESCE and IN:

select (2 = coalesce(1, 2, 3) as test  <<< this returns FALSE
GO
select (2 = coalesce(null, 1, 2, 3) as test  <<< this returns FALSE
GO
select (2 in (1, 2, 3)) as test  <<< this returns TRUE
GO
select (2 in (null, 1, 2, 3)) as test  <<< this returns TRUE
GO

Open in new window

with COALESCE you have to take care of the field's order and it returns just the first non-null value, with IN all values will we compare....

regards
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39234277
ok, "empty string" - that's different

I capitulate
0
 
LVL 4

Expert Comment

by:BAKADY
ID: 39237059
Are the author interested in these answers...???
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39240160
quasar_ee: it would help if you would actually interact with the experts.

"Either one of the columns has the value, or two, or all three, otherwise the value is empty string. That's how we can join those two tables using the value that exists in one of three columns and the value in the table2 column1."

That doesn't make a lot of sense, especially the part in bold.  You say you join the tables "using the value that exists in one of three columns".  That scenario doesn't cover the situation when you've got two or three columns populated simultaneously. As you haven't given us sufficient details on that, I'll ignore this situation in the continuation below.

"I have to create additional column in the table2 and populate it with the matching value from the column2 of the parent table1. "

Create the additional column, then use an UPDATE statement:

UPDATE T2
SET AdditionalColumn = T1.Column2
FROM Table2 T2
INNER JOIN Table1 T1 on T1.Column1 = T2.Column2

Open in new window

This would update the new column with the matching value from Column2 of the parent table1 based on Column2 in Table2.  Change that T1.Column1 = T2.Column2 part to any of the suggestions mentioned by the other experts (IN clause, COALESCE) depending on what you actually need.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question