chain of SQL joinings

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

chain of SQL joinings

/*Hi SAS Forum,*/

 

/*My co-worker has done a chain of sql left joins like below. Could you

help me to understand it*/

 

data one;

input id limit;

cards;

111 15000

222 25000

;

run;

 

 

data two;

input id balance;

cards;

111 500

222 300

;

run;

 

 

data three_dwo;

input id dwo_status $ 5-7;

cards;

111 dwo

222 no

;

run;

 

 

 

/*Below is the clumsy SQL joining*/

 

proc sql;

/*I can understand below piece in which we associate "balance" field to our "one" table*/

create table combined as

select a.*

,b.balance

from one as a left join

     two as b

on a.id=b.id

 

/*Below is the step I cannot understand. To which table we do the left joining of "three_dwo" table?*/

left join three_dwo as c

on a.id=c.id;

quit;

 

 /*Thanks, Mirisa*/


Accepted Solutions
Solution
‎01-12-2017 09:22 AM
Valued Guide
Posts: 860

Re: chain of SQL joinings

[ Edited ]

Because the code doesn't select anything from c.table the last part isn't changing anything, but you could do this:

 

proc sql;
create table combined as
select a.*,b.balance,c.dwo_status
from one a left join
     two b
on a.id=b.id
left join three_dwo c
on a.id=c.id;
quit;

 

 

The second join links table C to the table that was created between A and B.  Because they are both left joins table B and C both left join to table A. 

Which would pull in the field from three_dwo

View solution in original post


All Replies
Solution
‎01-12-2017 09:22 AM
Valued Guide
Posts: 860

Re: chain of SQL joinings

[ Edited ]

Because the code doesn't select anything from c.table the last part isn't changing anything, but you could do this:

 

proc sql;
create table combined as
select a.*,b.balance,c.dwo_status
from one a left join
     two b
on a.id=b.id
left join three_dwo c
on a.id=c.id;
quit;

 

 

The second join links table C to the table that was created between A and B.  Because they are both left joins table B and C both left join to table A. 

Which would pull in the field from three_dwo

Contributor
Posts: 38

Re: chain of SQL joinings

Posted in reply to Steelers_In_DC

Hi Steelers,

 

Thank you very much.

 

I still have a few questions which are embedded in red color in your response below.

 

proc sql;
create table combined as
select a.*,b.balance,c.dwo_status
from one a left join
     two b
on a.id=b.id
left join three_dwo c  /*this is the second join*/
on a.id=c.id;
quit;

 

 

The second join links table C to the table that was created between A and B (the table created between A and B is "combined". So, this means second join links table C to table "combined". Am I right? If this is right, then the resultant table due to the second join is what? Is the resultant table is again "combined".? ).  Because they are both left joins table B and C both left join to table A. 

Which would pull in the field from three_dwo

Respected Advisor
Posts: 4,173

Re: chain of SQL joinings

[ Edited ]

It's a bit hard to read but if you're really interested how SAS processes the query then options _method and _tree write quite a bit of information to the log. 

Running below code will illustrate what you can get in the SAS Log. If you really want to understand how to interprete the log messages then Google for some of the very informative white papers which give you a deep dive into this.

data one;
  ona=1;
  do id= 1 to 5;
    output;
  end;
run;

data two;
  balance=10;
  do id= 1 to 4, 6, 7;
    output;
  end;
run;

data three_dwo;
  dwo_status=100;
  do id= 1 to 3, 7,8;
    output;
  end;
run;

  
proc sql _method _tree feedback;
  create table combined as
    select a.*,b.balance,c.dwo_status
      from one a left join
        two b
        on a.id=b.id
      left join three_dwo c  /*this is the second join*/
  on a.id=c.id;
quit;

 

Contributor
Posts: 38

Re: chain of SQL joinings

Hi Patrick,

Thank you very much.

 

Mirisa

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 146 views
  • 1 like
  • 3 in conversation