How to write query to get data from other table for each columns in existing table.
2 Answers
You can left join three times on system_code:
select
d.id,
s_cat.full_name cat_code_full_name,
s_group.full_name group_code_full_name,
s_other.full_name other_code_full_name
from data_table d
left join system_code s_cat
on s_cat.value = d.cat_code and s.code = 1
left join system_code s_group
on s_group.value = d.group_code and s_group.code = 2
left join system_code s_other
on s_other.value = d.other_code and s_other.code = 3
To avoid repeating the joins, an alternative solution is to do conditional aggregation:
select
d.id,
max(case when s.value = d.cat_code and s.code = 1 then s.full_name end) cat_code_full_name,
max(case when s.value = d.group_code and s.code = 2 then s.full_name end) group_code_full_name,
max(case when s.value = d.other_code and s.code = 3 then s.full_name end) other_code_full_name
from data_table d
left join system_code s on s.value in (d.cat_code, d.group_code, d.other_code)
gtoup by d.id
4 Comments
Hemant
there are so many columns which are matched with the system_code table. so I do that much time Left join. there is another way?
GMB
@Hemant: I updated my answer with a solution that uses a unique left join.
Hemant
@GBM, Thank you so much, you are genius. highly appreciated. Next time I make sure to add only relevant tag and following the rules before asking any question. once again Thank you lot.
Hemant
Thank you for letting me know about the accept it. I got another way from my colleague SELECT d.id, ( SELECT s.full_name FROM system_code s WHERE s.system_code = 1 AND s.value = d.cat_code ) AS cat_code_full_name FROM data_table d
I guess the problem you are facing here is, how to get full name for all 3 columns. 1 of the method is to join SYSTEM_CODE table thrice -
SELECT DT.ID
,SC1.FULL_NAME CAT_CODE_FULL_NAME
,SC2.FULL_NAME GROUP_CODE_FULL_NAME
,SC3.FULL_NAME OTHER_CODE_FULL_NAME
,DT.PRODUCT
FROM DATA_TABLE DT
JOIN SYSTEM_CODE SC1 ON SC1.VALUE = DT.CAT_CODE
JOIN SYSTEM_CODE SC2 ON SC2.VALUE = DT.CAT_CODE
JOIN SYSTEM_CODE SC3 ON SC3.VALUE = DT.CAT_CODE
1 Comment
Hemant
there are so many columns which are matched with the system_code table. so I do that much time Left join. there is another way?
sql-serverandoracle. Please add only the one that is relevant.