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
494 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 48

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

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 48

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 48

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

739 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