We help IT Professionals succeed at work.

logic to match different columns

sam2929
sam2929 asked
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

Commented:
in the where clause you can write
where (table1.009 = table2.id) or (table1.Z03=table2.id) or (table1.Z09 = table2.id)
awking00Information 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.
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?
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;

Open in new window

awking00Information 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;