SQL Left join on different columns considering specific conditions

Question

I believe this is an easy one. Just getting started on SQL, so I am finding it a bit tricky. So I am using SQL on SAS, and I want to join two tables but on different columns based on a value of a column. Practical example:

Proc sql;
create table new_table_name as select 
a.proposal_code as new_name_proposal_code,
a.1st_client_code as new_name_1st_client_code,
a.2nd_client_code as new_name_2nd_client_code,
a.3rd_client_code as new_name_3rd_client_code,
a.4th_client_code as new_name_4th_client_code,
a.product_type as new_name_product_type,
b.2nd_client_code
from existing_table a
left join existing table b (on b.2nd_client_code=a.2nd_client_code and a.product_type = "clothes") or 
left join existing table b (on b.2nd_client_code=a.3rd_client_code and (a.product_type = "cars" or a.product_type = "bikes"));
quit;

So this is the code that I'm using at the moment, and the goal is to join table a and table b using b.2nd client code = a.2nd client code if the product type from table a is = to "clothes", and if the product type from table a is either "cars" or "bikes", join table a and table b using b.2nd client code = a.3rd client code. Basically, look at two different "on's" regarding the specific product type. When joining these two tables, if one row has product type "clothes", I want it to look at the 2nd client code, if it is either "cars" or "bikes", look at the 3rd client code.

Hope I made it clear. The error I am getting at the moment is "expecting an on". Is it a problem of syntax?

I dont know about `sas`, but in sql this is invalid: `left join existing table b` . Also the alias `b` should be unique, the second `left join` should have another alias than the first. — Luuk, Jul 11 '20 at 09:57
Sounds like you just want to evaluate a complicated expression for the ON criteria. Unless you want to have the same two records from A and B produce two different outputs when both criteria are satisfied. — Tom, Jul 11 '20 at 21:08
Your variable names are not valid. Names in SAS (and most languages) cannot start with a number. — Tom, Jul 11 '20 at 21:15
You can add a case to your SQL statements, including joins. This would be infinitely simpler if you posted some sample data and expected output. — Reeza, Jul 13 '20 at 00:43
And is it possible that both OR conditions can be true at the same time? I suspect they could be.... — Reeza, Jul 13 '20 at 00:44

Gordon Linoff · Answer 1 · 2020-07-13T00:46:29.373

Yes. The parentheses before the on is not correct. Your query has other issues as well. I think you want:

create table new_table_name as
    select a.proposal_code as new_name_proposal_code,
           a.1st_client_code as new_name_1st_client_code,
           a.2nd_client_code as new_name_2nd_client_code,
           a.3rd_client_code as new_name_3rd_client_code,
           a.4th_client_code as new_name_4th_client_code,
           a.product_type as new_name_product_type,
           coalsesce(bc.2nd_client_code, bcb.2nd_client_code)
    from existing_table a left join
         existing_table bc
         on bc.2nd_client_code = a.2nd_client_code and
            a.product_type = 'clothes' left join 
         existing_table bcb
         on bcb.2nd_client_code = a.3rd_client_code and
            a.product_type in ('cars', 'bikes');

Notes:

No parentheses before the on clause.
No or left join. or is a boolean operator. left join is an operator on sets (i.e. tables and results sets). The don't mix.
No repeated table aliases.
You want to combine the two code, so you need something like coalesce() in the select.
The SQL delimiter for strings is the single quote, not the double quote.
in is simpler than a string of or conditions.

SAS uses either double or single quotes around strings, even in PROC SQL, unless you explicitly tell it differently via an option in the PROC SQL statement. — Tom, Jul 11 '20 at 21:12
@Gordon Linoff, A single quote is missing before the word `bike`. — whymath, Jul 13 '20 at 00:42

score 1 · Answer 2 · answered Jul 11 '20 at 21:23

Sounds like you just want a complex ON criteria and not two joins. Something like this:

proc sql;
create table new_table_name as 
select 
   a.proposal_code as new_name_proposal_code
  ,a.client_code1 as new_name_client_code1
  ,a.client_code2 as new_name_client_code2
  ,a.client_code3 as new_name_client_code3
  ,a.client_code4 as new_name_client_code4
  ,a.product_type as new_name_product_type
  ,b.client_code2 as new_name_other_client_code2
from tableA a
left join tableB b
  on (b.client_code2=a.client_code2 and a.product_type = "clothes")
  or (b.client_code2=a.client_code3 and a.product_type in ("cars","bikes"))
;
quit;

For a better answer post example inputs and desired output.

SQL Left join on different columns considering specific conditions

2 Answers2