mysql> select * from list;
+------+------+------+
| lID | sID | iID |
+------+------+------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 1 | 4 |
+------+------+------+
4 rows in set (0.00 sec)
mysql> select * from item;
+------+---------+
| iID | name |
+------+---------+
| 1 | cup |
| 2 | plate |
| 3 | book |
| 4 | flowers |
+------+---------+
4 rows in set (0.00 sec)
mysql> select * from shelf;
+------+---------+
| sID | name |
+------+---------+
| 1 | shelf 1 |
+------+---------+
1 row in set (0.00 sec)
mysql> SELECT CONCAT('SELECT s.name AS shelf_name, ',GROUP_CONCAT(' SUM(IF(i.iID = ', i.iID,', 1, 0)) AS item_', i.iID), ' FROM item i JOIN list l USING (iID) JOIN shelf s USING (sID);') INTO @sql FROM (SELECT DISTINCT i.name,i.iID,l.sID FROM item i JOIN list l USING (iID) JOIN shelf s USING (sID)) AS i;
Query OK, 1 row affected (0.00 sec)
mysql> PREPARE stmt FROM @sql;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> EXECUTE stmt;
+------------+--------+--------+--------+--------+
| shelf_name | item_1 | item_2 | item_3 | item_4 |
+------------+--------+--------+--------+--------+
| shelf 1 | 1 | 1 | 1 | 1 |
+------------+--------+--------+--------+--------+
1 row in set (0.00 sec)
mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)
You may be able to slightly optimize that, just did it quickly.
I don't believe you can do this without using a prepared statement, though (ie. not in a regular query).
Personally, when you start making your queries this complex, unless it's absolutely necessary, I'd opt for the simpler method and parse the data in your application (ie. PHP) instead
