topic Join Same Table More Than Once in SAS Programming

Join Same Table More Than Once

req41273 — Wed, 26 Jun 2019 02:01:47 GMT

I'm aiming for a result set where I'll see a column a in the MDM.MPI_MEMPHONEWC Table two times. Therefore,
I've put in my code an inner join on that table that joins the tables to itself.
My question is is what I've got below follow true to what I'm trying to accomplish with MDM.MPI.MEMPHONEWC table.

NI'm not looking for replies to know what my table column values are.  I'm looking for just an overall code review
oof does it make sense.

FROM
HSDREPT.MEMBER_ADDRESS as MA 
inner join HSDREPT.MEMBER_MASTER as MM
    on MM.SEQ_MEMB_ID = MA.SEQ_MEMB_ID 
inner join HSDREPT.member_elig_history as meh
    on MM.SEQ_MEMB_ID = MEH.SEQ_MEMB_ID 
inner join HSDREPT.PROV_MASTER as PM
    on PM.SEQ_PROV_ID = MEH.SEQ_PROV_ID 
inner join HSDREPT.PROV_ADDRESS as PA  
    on PM.SEQ_PROV_ID = PA.SEQ_PROV_ID
LEFT JOIN MDM.MPI_MEMIDENTWC AS MID
    on MM.SUBSCRIBER_ID = MID.IDNUMBER
inner join MDM.MPI_MEMADDRWC as MAD
    on MAD.MEMRECNO = MID.MEMRECNO 
inner join MDM.MPI_MEMPHONEWC as MP
    on MAD.MEMRECNO = MP.MEMRECNO
inner join MDM.MPI_MEMPHONEWC2 AS MPH
    on MP.MEMRECNO = MPH.MEMRECNO

Re: Join Same Table More Than Once

SASKiwi — Wed, 26 Jun 2019 02:39:05 GMT

It is perfectly OK to join the same table more than once in a query, but when doing that there should be something different about the second or subsequent joins. From your code snippet I can't see anything different but you will need to change this:

MDM.MPI_MEMPHONEWC2 /* Why the 2 on the end? you dont need it if you are reading the same table */

Re: Join Same Table More Than Once

req41273 — Wed, 26 Jun 2019 13:50:33 GMT

My goal is to have the same table available for different where and select conditions. So that's is why I put the 2 at the end.

inner join MDM.MPI_MEMPHONEWC as MP

on MAD.MEMRECNO = MP.MEMRECNO

inner join MDM.MPI_MEMPHONEWC2 AS MPH

on MP.MEMRECNO = MPH.MEMRECNO

Re: Join Same Table More Than Once

req41273 — Wed, 26 Jun 2019 14:07:08 GMT

But, I think my alias names being different should differentiate the same table as being two different tables now. correct?

Re: Join Same Table More Than Once

SASKiwi — Mon, 01 Jul 2019 20:11:19 GMT

@req41273 - Yes, differentiate by alias, table name has to stay the same.