mysql 查询具有公共(public)标准化表的表

Question

我有三张 table

table1 是其他表的通用规范化表，其中“id”是其他表的外键

表1

<小时/>

     id     type         value
    --------------------------- 
     1      category    patient
     2      category    control 
     3      pain        mild  
     4      pain        severe 
     5      pain        normal
     6      pelvic_pain mild
     7      pelvic_pain normal
     8      pelvic_pain severe

表2

<小时/>

    id | patient_code | category_id | pain_id
    ------------------------------------
    1     asd1          1              5
    2     asd2          1              4
    3     asd3          2              3

表3

<小时/>

    id | patient_id | pelvic_pain_id  
    ------------------------------
    1     1            6             
    2     2            6  
    3     3            8              

我想做 mysql 查询以获得以下非规范化输出

输出

<小时/>

    id | patient_code | category |     pain    | pelvic_pain
    --------------------------------------------------------
    1     asd1          patient        severe     mild
    2     asd2          patient        severe     severe
    3     asd3          control        mild       severe

Answer 1

http://sqlfiddle.com/#!9/817b8/2

SELECT t2.id,
       t2.patient_code,
       t1.value category,
       t1_.value pain,
       t1_3.value pelvic_pain

FROM table2 t2
LEFT JOIN table1 t1
ON t2.category_id = t1.id
LEFT JOIN table1 t1_
ON t2.pain_id = t1_.id
LEFT JOIN table3 t3
ON t2.id = t3.patient_id
LEFT JOIN table1 t1_3
ON t3.pelvic_pain_id = t1_3.id