INNER JOIN multiple columns to same column from another table

I got hmmd_prm table that is:

| id |  hmmd_id  |  prm1  |  prm2  |  prm3  |  prm4  |  prm5  |  prm6  |  prm7  |  prm8  |  prm9  |  prm10 |
| 1  |     2     |   1    |   2    |   3    |   1    |   2    |   3    |   1    |   2    |  null  |  null  |
| 2  |     3     |   4    |   3    |   2    |   1    |   4    |   3    |   2    |   1    |   4    |   3    |

and lab_prm table that is:

| prm_id |  prm_adi  |  prm_adgr  |  prm_udgr  |  prm_birim  |  prm_ctrl  |
|   1    |    pH     |
|   2    |    O2     |
|   3    | peroxide  |
|   4    |    hum    |

and main_hmmd table that has names and pulls names using hmmd_id

I’m trying to get a result like this:

| id |  hmmd_id  |  prm1  |  prm2  |  prm3  |  prm4  |  prm5  |  prm6  |  prm7  |  prm8  |  prm9  |  prm10 |
| 1  |   BUGDAY  |   pH   |   O2   | prxide |   pH   |   O2   | prxide |   pH   |   O2   |  null  |  null  |

I am using something like this:

SELECT hp.id, mh.hmmd_adi, lp1.prm_adi, lp2.prm_adi, lp3.prm_adi, lp4.prm_adi, lp5.prm_adi, lp6.prm_adi, lp7.prm_adi, lp8.prm_adi, lp9.prm_adi, lp10.prm_adi
FROM std_tbl_hmmd_prm AS hp
INNER JOIN std_tbl_main_hmmd AS mh ON mh.id = hp.hmmd_id
INNER JOIN std_tbl_lab_prm AS lp1 ON hp.prm1 = lp1.prm_id
INNER JOIN std_tbl_lab_prm AS lp2 ON hp.prm2 = lp2.prm_id
INNER JOIN std_tbl_lab_prm AS lp3 ON hp.prm3 = lp3.prm_id
INNER JOIN std_tbl_lab_prm AS lp4 ON hp.prm4 = lp4.prm_id
INNER JOIN std_tbl_lab_prm AS lp5 ON hp.prm5 = lp5.prm_id
INNER JOIN std_tbl_lab_prm AS lp6 ON hp.prm6 = lp6.prm_id
INNER JOIN std_tbl_lab_prm AS lp7 ON hp.prm7 = lp7.prm_id
INNER JOIN std_tbl_lab_prm AS lp8 ON hp.prm8 = lp8.prm_id
INNER JOIN std_tbl_lab_prm AS lp9 ON hp.prm9 = lp9.prm_id
INNER JOIN std_tbl_lab_prm AS lp10 ON hp.prm10 = lp10.prm_id

It is working as I wanted but I am pretty sure there is a way better solution to this.

How can I make this simplier?

Thanks in advance.

Update: What I am trying to achieve is, in the hmmd_prm table I have some materials as hmmd_id and user will apply some parameters to those parameters. I made 10 columns for parameters so user can assign max 10 parameters but might also assign 1 or 2 and rest null.

This might not be the best solution to this, since this is my first time doing something like this but I simply didn’t want the result showing as multiple entries of same material with different parameters in each row like below.

| id |  hmmd_id  |  prms  |
| 1  |     2     |   1    |
| 2  |     2     |   2    |
| 3  |     2     |   3    |
| 4  |     2     |   1    |
| 5  |     2     |   2    |
| 6  |     2     |   3    |
| 7  |     2     |   1    |

Answer

SQL and Relational Databases are opposite sides of the same coin, they both have normalised data structures baked in to their design.

This is just one sign that the structure your dealing with is not a good fit for SQL and so not a good fit for Relational Databases. (A code smell.)

As such, some repetition can be removed by normalising the structure on the fly, and then de-normalising again if you must.

SELECT
  hp.id,
  mh.hmmd_adi,
  MAX(CASE WHEN pivot_prm.col_id =  1 THEN lp.prm_adi END)   AS prm1,
  MAX(CASE WHEN pivot_prm.col_id =  2 THEN lp.prm_adi END)   AS prm2,
  MAX(CASE WHEN pivot_prm.col_id =  3 THEN lp.prm_adi END)   AS prm3,
  MAX(CASE WHEN pivot_prm.col_id =  4 THEN lp.prm_adi END)   AS prm4,
  MAX(CASE WHEN pivot_prm.col_id =  5 THEN lp.prm_adi END)   AS prm5,
  MAX(CASE WHEN pivot_prm.col_id =  6 THEN lp.prm_adi END)   AS prm6,
  MAX(CASE WHEN pivot_prm.col_id =  7 THEN lp.prm_adi END)   AS prm7,
  MAX(CASE WHEN pivot_prm.col_id =  8 THEN lp.prm_adi END)   AS prm8,
  MAX(CASE WHEN pivot_prm.col_id =  9 THEN lp.prm_adi END)   AS prm9,
  MAX(CASE WHEN pivot_prm.col_id = 10 THEN lp.prm_adi END)   AS prm10
FROM
  std_tbl_hmmd_prm   AS hp
INNER JOIN
  std_tbl_main_hmmd  AS mh
    ON mh.id = hp.hmmd_id
CROSS APPLY
(
  VALUES
    ( 1, hp.prm1 ),
    ( 2, hp.prm2 ),
    ( 3, hp.prm3 ),
    ( 4, hp.prm4 ),
    ( 5, hp.prm5 ),
    ( 6, hp.prm6 ),
    ( 7, hp.prm7 ),
    ( 8, hp.prm8 ),
    ( 9, hp.prm9 ),
    (10, hp.prm10)
)
  AS pivot_prm(col_id, prm_id)
INNER JOIN
  std_tbl_lab_prm   AS lp
    ON pivot_prm.prm_id = lp.prm_id
GROUP BY
  hp.id,
  mh.hmmd_adi

Personally I’d advise not doing the GROUP BY and MAX(CASE) to de-normalise the results, and instead just use…

SELECT
  hp.id,
  mh.hmmd_adi,
  pivot_prm.col_id,
  lp.prm_adi
FROM
  std_tbl_hmmd_prm   AS hp
INNER JOIN
  std_tbl_main_hmmd  AS mh
    ON mh.id = hp.hmmd_id
CROSS APPLY
(
  VALUES
    ( 1, hp.prm1 ),
    ( 2, hp.prm2 ),
    ( 3, hp.prm3 ),
    ( 4, hp.prm4 ),
    ( 5, hp.prm5 ),
    ( 6, hp.prm6 ),
    ( 7, hp.prm7 ),
    ( 8, hp.prm8 ),
    ( 9, hp.prm9 ),
    (10, hp.prm10)
)
  AS pivot_prm(col_id, prm_id)
INNER JOIN
  std_tbl_lab_prm   AS lp
    ON pivot_prm.prm_id = lp.prm_id

This will make many subsequent queries much simpler.