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
481 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 40

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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now