Left Join with 2 references

Question

Post reply

Left Join with 2 references

ROBOYJ

Valued Member

Points: 67
More actions
September 8, 2015 at 6:37 am

#304220

If this has been asked, please excuse me.
I need to join 2 tables but the join needs to account for 2 seperate columns.
for example:
select
a. type
a. prod_code
a. prod_type
b. division
from table1 a
left join table2 b
on a. prod_code = b. prod_code
and a. prod_type = b. prod_type
The issue is that you may have only the prod_code or prod_type and null value for the other in table1.
Ideally I want it to check for both then if 1 isn't available then it draws the division of the available. having both or one or the other determines the division it falls under.
Is this possible?
Thanks in advance.
edit for spelling...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Try

left join table2 b on a. prod_code = b.prod_code or a. prod_type = b. prod_type

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ROBOYJ Valued Member Points: 67 More actions · Answer 2

Thanks Gila.

This still brings back more records than the original :S

Y.B. SSChampion Points: 11649 More actions · Answer 3

Click here on how to post examples.[/url]

SELECT quote FROM brain WHERE original = 1
0 rows returned

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Really not much more advice I can offer without having some data to work with.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 5

As stated by others, it's not very clear what it is your trying to accomplish...

On the off chance that I did correctly understand the requirement, give the following a try.

SELECT

a.type,

a.prod_code,

a.prod_type,

b.division

FROM

table1 a

LEFT JOIN table2 b

ON a.prod_code = COALESCE(b.prod_code,a.prod_code)

AND a.prod_type = COALESCE(b.prod_type,a.prod_type)

AND (b.prod_code IS NOT NULL OR b.prod_type IS NOT NULL) ;