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] but that doesn’t seem to work. I’ve also reviewed these two stackoverflow questions [Question1] and [Questions2] they didn’t really seem to be what I was looking for.
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)