How to replace reference ID with value

Question

Let's say I have two tables:

+------------------------------------+
|              `houses`              |
+----+-------+-------+-------+-------+
| id | house | room1 | room2 | room3 |
+----+-------+-------+-------+-------+
|  1 | a     |     1 |     1 |     2 |
|  2 | b     |     1 |     3 |     1 |
|  3 | c     |     2 |     2 |     1 |
+----+-------+-------+-------+-------+

+------------------------+
|        `status`        |
+-------------+----------+
| status_code |  status  |
+-------------+----------+
|           1 | empty    |
|           2 | occupied |
|           3 | full     |
+-------------+----------+

Now I want to change room1, room2 and room3's ids to the status from TABLE status like this:

+---------------------------------------------+
|                   `houses`                  |
+----+-------+----------+----------+----------+
| id | house |  room1   |  room2   |  room3   |
+----+-------+----------+----------+----------+
|  1 | a     | empty    | empty    | occupied |
|  2 | b     | empty    | full     | empty    |
|  3 | c     | occupied | occupied | empty    |
+----+-------+----------+----------+----------+

I know one solution, but I believe there is an easier way to do the same:

SELECT
    h.*,
    s1.status AS room1,
    s2.status AS room2,
    s3.status AS room3
FROM houses h
JOIN status s1 ON h.room1 = s1.status_code
JOIN status s2 ON h.room2 = s2.status_code
JOIN status s3 ON h.room3 = s3.status_code

UPDATE

MySQL pivot table solution is not suitable for me because the above example is a simplified version what I use. There are more than ten different status and writing a 40 line CASE function for a single room is not an opportunity. The status must come from the status table.

Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — Raymond Nijland, Jun 25 '19 at 09:40
It is not a 100% matching duplicate but the question (can) use the same approach.. `SELECT MAX(CASE WHEN houses. room1 = 1 THEN 'empty' .... END) AS room1... GROUP BY id, house` — Raymond Nijland, Jun 25 '19 at 09:42
I think your solution is quick to write, more intuitive and easier. — Alberto Moro, Jun 25 '19 at 10:08
Your solution is based on that I know exactly each status code. The propblem is that these tables are simplified tables and one room could have much more status possibilities than the three I wrote so the statuses have to come from the pivot table. — LaCorb, Jun 25 '19 at 10:21
Your `house` table should be two tables, because 1 house can have 1000 rooms, so you should not create additional columns, but insert new rows to room table. — Justinas, Jun 25 '19 at 10:53
In this problem that's not the case. Let's assume that there is exactly 3 rooms per house. — LaCorb, Jun 25 '19 at 10:57
'but I believe there is an easier way to do the same' - there isn't IMHO given the design. — P.Salmon, Jun 25 '19 at 11:26
Yes, I also start to think that my solution is the easiest from what I saw in the postst — LaCorb, Jun 25 '19 at 13:31

score 0 · Answer 1 · answered Jun 25 '19 at 11:23

You can approach this as

SELECT id,house,
(
  CASE
    WHEN room1 > 0 THEN (SELECT status FROM status WHERE status_code=room1)
 END) AS room1,
(
  CASE
    WHEN room2 > 0 THEN (SELECT status FROM status WHERE status_code=room2)
 END) AS room2,
(
  CASE
    WHEN room3 > 0 THEN (SELECT status FROM status WHERE status_code=room3)
 END) AS room3
 FROM houses

Live DEMO

How to replace reference ID with value

1 Answers1