SQL JOIN - one column twice

[PM23]

Hi guys,

I have two tables and I need to get info from one table to the second one, so I used JOIN. But I need to add index from one column from table #2 joined on two columns in table #1 (e.g.: I need to mark apples and pears as a fruit, fruit index is info from table #2, apple index is in column A in table #1 and pear index is in column B in table #1). Is it possible to do it? And is it possible to do it in one step?


Thank you.

[Kurt_Bremser]

Please show us your input data, preferably in data steps with datalines, so we can easily use it for testing.

Also show the intended result.

[PM23]

Let's say I have two tables:

First one - columns Apple_name, Pear_name (empty, if it is apple and vice versa), Weight

Second one - columns Fruit_index, Name

So I tried:

proc sql;
	create table test as
		select a.*, b.fruit_index
			from First_table a left join Second_table b 
				on a.Apple_name=b.Name and a.Pear_name=b.Name;
quit;

But it seems that just one part joined.

Intended result:
First row - Apple_name: Golden Smith, Pear_name:., Weight: 300, Fruit_index: Apple
Second row - Apple_name:., Pear_name: Gren Anjou Weight: 280, Fruit_index: Pear
Third row - Apple_name:., Pear_name:., Weight: 250, Fruit Index:.

[Kurt_Bremser · Accepted solution]

Just replace the "and" in your condition with "or":

data have1;
infile datalines dlm=',' dsd truncover;
input apple_name :$20. pear_name :$20. weight;
datalines;
Golden Smith,,300
,Gren Anjou,280
,,250
;

data have2;
infile datalines dlm=',' dsd truncover;
input fruit_index :$10. fruit_name :$20.;
datalines;
Apple,Golden Smith
Pear,Gren Anjou
;

proc sql;
create table want as
select a.*, b.fruit_index
from have1 a left join have2 b
on a.apple_name = b.fruit_name or a.pear_name = b.fruit_name;
quit;

[PM23]

It was that simple! Thank you very much.