0

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.

0

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.

  • I think they need 2 joins to service, one on d.service (as you did) and another on d.dependent_service – ypercubeᵀᴹ Apr 10 '18 at 12:43
  • I think you are right.. I needed to add one more join as you suggested and it worked. – kaounKaoun Apr 10 '18 at 16:29
  • Can you show us your final solution please? – Vérace Apr 10 '18 at 17:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.