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.