Postgres Join multiple table to replace id of parent table with value

Question

I have following tables:

dependency
===========
id  service  dependent_service  dependency_type
--  -------  -----------------  ---------------
1   9        2                  16
5   2        7                  15


dependency_type
===========
id  name
--  --------
15  internal
16  external


service
==========
s_id  s_name  description  s_type
----  ------  -----------  ------
2     abc     ABC          1
9     cde     CDE          2
7     xyz     XYZ          3


service_type
==============
id  name
--  ---
1   api
2   db

I am basically trying to replace the ids in parent table (dependency) with the name and value from the main table. Ex. replace dependent_service (2) with value (abc). Similarly for oter fields. So my result table looks like this:

Result table
===============
r_id  r_service  r_dependent_service  r_dependency_type  r_service_description  r_dependent_service_description 
----  ---------  -------------------  -----------------  ---------------------  -------------------------------
1     cde        abc                  external           CDE                    ABC
5     abc        xyz                  internal           ABC                    XYZ

I tried to JOIN all tables using WHERE clause, but its resulting duplicate rows.

Select * from dependency as d INNER JOIN dependencytype as dt ON d.dependency_type = dt.id INNER JOIN 
(service as s INNER JOIN servicetype as
st ON s.service_type = st.id) ON 1=1

Can anyone please help me here. Let me know if you need more information.

Answer 1

This is what you want:

select *
from dependency as d
join service as s on d.service = s.s_id 
join service_type as st on s.s_type = st.id 
join dependency_type as dt on d.dependency_type = dt.id; 

dbfiddle here.