Join Same Table More Than Once

[req41273]

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

[SASKiwi · Accepted solution]

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 */

[req41273]

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 

[req41273]

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

[SASKiwi]

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