0

How to write query to get data from other table for each columns in existing table.

Please find attached image for tables and sample data requirement.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Hemant
  • 1
  • 1
  • I removed conflicting tags `sql-server` and `oracle`. Please add only the one that is relevant. – GMB Oct 22 '19 at 01:15
  • Possible duplicate of [SQL JOIN and different types of JOINs](https://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) – Dai Oct 22 '19 at 01:20
  • I am a new in SQL i tried my best but i can't get the result. thus I need help from you guys. – Hemant Oct 22 '19 at 01:50
  • Most people here want sample table data and expected result as formatted text, not as images. – jarlh Oct 22 '19 at 06:57
  • Make it easy (and possible) to assist you: [mcve]. – jarlh Oct 22 '19 at 06:57

2 Answers2

1

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
GMB
  • 216,147
  • 25
  • 84
  • 135
  • 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? – Hemant Oct 23 '19 at 00:31
  • @Hemant: I updated my answer with a solution that uses a unique left join. – GMB Oct 23 '19 at 00:45
  • @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 Oct 23 '19 at 04:12
  • 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 – Hemant Oct 24 '19 at 02:46
0

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
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
  • 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? – Hemant Oct 23 '19 at 00:31