SQL query to select mapping table values?

Question

I have a table named Patient in which I have columns like

ID Disease1  Disease2  Disease3
         ----------
1     4        3         2
         ----------
2     2        5
         ----------    
3     6
         ----------
4     1

These are mapping values which I got from table Disease, in which disease names are placed like

1  hypertension
2  niddm
3  allergy  
4  cough
5  floo
6   vv
etc

Now I want sql query to select

ID Disease1  Disease2  Disease3
           ----------
1   cough    allergy     niddm
           ----------
2   niddm    floo
           ----------
3    vv
           ----------
4     HT

Please keep in mind that I have table mapped with 4,5 tables and I want original values in place of ids from all of them.

Is this already in place or are you in the process of developing the database schema? If so I would revisit your design and have a new table called PatientDisease - your current setup is a poor design which requires you to join your disease table multiple times. — Dave Richardson, May 29 '13 at 08:31

score 3 · Answer 1 · answered May 29 '13 at 06:59

You need to join table Disease thrice on table Patient since there are three column from Patient that are dependent on Disease

SELECT  a.ID,
        b.Disease AS Disease1,
        c.Disease AS Disease2,
        d.Disease AS Disease3
FROM    Patient a
        LEFT JOIN Disease b
            ON a.Disease1 = b.ID
        LEFT JOIN Disease c
            ON a.Disease2 = c.ID
        LEFT JOIN Disease d
            ON a.Disease3 = d.ID

To further gain more knowledge about joins, kindly visit the link below:

Visual Representation of SQL Joins

SQL query to select mapping table values?

1 Answers1