[SOLVED] Show Result based on mapped tables

Issue

I’ve 2 mysql table, the first table Activity stores a patient activity level as an integer, another table maps each integer with a description. I am trying to create a select query that gives me the description for the patients activity instead of the integer value.

Activity Table
id|pid|bathing|cognition|housekeeping|dressing|feeding|meal
1 | 20|  4    |   4     |      2     |    6   |   8   |   4
2 | 40|  4    |   2     |      4     |    4   |   6   |   6  

Mapping Table
value|description
2    | Independent
4    | Minimal 
6    | Moderate
8    | Maximum

Desired Query Result

id|cid|bathing|cognition|housekeeping|dressing|feeding|meal
1 | 20|Minimal|Minimal  |Independent |Moderate|Maximum|Minimal

I’ve looked into using mysql [CASE][1] but that doesn’t seem to work. I’ve also reviewed these two stackoverflow questions [Question1][2] and [Questions2][3] they didn’t really seem to be what I was looking for.

Solution

The following code uses your mapping table to display the correct description. MYSQL documentation on select subquery.

Select id, pid,
(Select description from mapping where value=a.bathing) as 'bathing',
(Select description from mapping where value=a.cognition) as 'cognition',
(Select description from mapping where value=a.housekeeping) as 'housekeeping',
(Select description from mapping where value=a.dressing) as 'dressing',
(Select description from mapping where value=a.feeding) as 'feeding',
(Select description from mapping where value=a.meal) as 'meal'
From activity a

Answered By – WorkSmarter

Answer Checked By – Timothy Miller (BugsFixing Admin)

Leave a Reply

Your email address will not be published.