Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
499 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
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 2000 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Integration Management Part 2

877 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