I need help joining tables

user7983964 Published at Dev

user7983964

I need help on joining these three fields,

Party table where all party information stored: it just a list of Party Name and ID. and it look like this

PartyID PartyName
14      CIDS
15      DTL
16      CRTS
17      VIVAR
18      CT INC
19      AMC INC
20      YIBG
38      LLA INC
35      GFRC
43      UC AM    
44     CIDW
46       KIN TECH
47      MAIN RP
85      CCDS

I have this Responsible Party table as

  RPID  PID PartyID
   38   42   85

I have this subparty table

SPID    RPID PartyID
14      38    43
15      38    44
16      38    45
17      38    45
18      38    46
19      38    47
20      38    47

I need your help on displaying my data as

RPID    PartyID  PartyName  SubID   SubParty
38       43      UCAM       14      CIDS    
38       44      CIDW       15      DTL
38       45                 16      CRTS
38       45                 17      VIVAR   
38       46    KIN TECH     18     CT INC
38       47                 20      YIBG
38       85     CCDS

forpas

You need to join subparty to responsibleparty and 2 copies of party:

select sp.rpid, sp.partyid, p.partyname, sp.spid, pp.partyname subparty
from subparty sp
left join responsibleparty rp on rp.rpid = sp.rpid
left join party p on p.partyid = sp.partyid
left join party pp on pp.partyid = sp.spid

See the demo.
Results:

> rpid | partyid | partyname | spid | subparty
> ---: | ------: | :-------- | ---: | :-------
>   38 |      43 | UC AM     |   14 | CIDS    
>   38 |      44 | CIDW      |   15 | DTL     
>   38 |      45 | null      |   16 | CRTS    
>   38 |      45 | null      |   17 | VIVAR   
>   38 |      46 | KIN TECH  |   18 | CT INC  
>   38 |      47 | MAIN RP   |   19 | AMC INC 
>   38 |      47 | MAIN RP   |   20 | YIBG

Collected from the Internet

Please contact [email protected] to delete if infringement.