Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
498 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 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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

661 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