We help IT Professionals succeed at work.

# logic to match different columns

on
HI,
I Have three columns

Table a

009 Z03 Z09
1    1       4
2
3    2       6
4    5      7

Then TABLE B
Id   KEY
1      001
2      002
5    0050
6    0060

I Want to join A with B so that if table  id donâ€™t match table A column 009 then match against Z03 else Z09

So results should be

009     Z03    Z09     key
1            1       4        001
2                               002
3            10       6      0060 ( In this case id =Z09)
4               5      7       0050 ( In this case id =Z03)
Comment
Watch Question

## View Solution Only

Commented:
in the where clause you can write
where (table1.009 = table2.id) or (table1.Z03=table2.id) or (table1.Z09 = table2.id)
Information Technology Specialist

Commented:
>>So results should be

009     Z03    Z09     key
1            1       4        001
2                               002
3            10       6      0060 ( In this case id =Z09)
4               5      7       0050 ( In this case id =Z03)<<

Explain why this
3            10       6      0060 ( In this case id =Z09)
should not be this
3              2       6      0060 ( In this case id =Z09)
i.e. Where did 10 come from?

Commented:
can you post the full query what you are trying.. this is looking confusing to me.

Commented:
i would propose to transform the data in table A such that there's only 1 joining column as an additional column and a priority column:

ID Priority 009 Z03 Z09
1  1           1    1       4
2  1           2
3  1           3    2       6
4  1           4    5       7
1  2           1    1       4
2  2           3    2       6
5  2           4    5       7
4  3           1    1       4
6  3           3    2       6
7  3           4    5      7

so that in this case, you join by ID and take the smallest available priority value... will this work?
Commented:
Hi

Here's code to do exactly what you have asked.  However, the loginc in the "ON" statement can get tricky if there are more than 3 coumns.  Also, the merge uses a Cartesian Product, so large datasets may take some time.

``````DATA TableA;
INFILE CARDS TRUNCOVER;
informat _009 Z03 Z09 Best4.;
INPUT _009 Z03 Z09 ;
Cards;
1 1 4
2
3 10 6
4 5 7
;
RUN;

DATA TableB;
INFILE CARDS TRUNCOVER;
format key \$4.;
INPUT id 1. +1 key \$4.;
CARDS;
1 001
2 002
5 0050
6 0060
;
RUN;

PROC SQL;
create table final as
select a.*, b.key from TableA as a
left join
TableB as b
on (a._009 = b.id)
or (a._009 ~= b.id and a.Z03 = b.id)
or (a._009 ~= b.id and a.Z03 ~= b.id and a.Z09 = b.id)
order by a._009;
``````
Information Technology Specialist

Commented:
select a.col1, a.col2, a.col3, b.key
from taba a inner join tabb b on a.col1 = b.id
union all
select a.col1, a.col2, a.col3, b.key
from taba a inner join tabb b on a.col2 = b.id
where b.id not in (select col1 from taba)
union all
select a.col1, a.col2, a.col3, b.key
from taba a inner join tabb b on a.col3 = b.id
where b.id not in (select col1 from taba)
or b.id not in (select col2 from taba)
order by col1;