MYSQL database help!
Hey all, I'm working on a database design, which I have pretty air tight. But I just can't seem to get the query right. Maybe some other eyeballs could help.
First off, here's the tables:
select * from list;
+-----+-----+-----+
| lID | sID | iID |
+-----+-----+-----+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 1 | 4 |
+-----+-----+-----+
select * from item;
+-----+---------+
| iID | name |
+-----+---------+
| 1 | cup |
| 2 | plate |
| 3 | book |
| 4 | flowers |
+-----+---------+
select * from shelf;
+-----+---------+
| sID | name |
+-----+---------+
| 1 | shelf 1 |
+-----+---------+
Now here is the tricky part, I want the result to look like, this
|shelf|item1|item2|item3|item4|
basically, anytime I add a new time on I won't have to go in, and manually add a new column in.
This is as close as I have it now:
select s.name, GROUP_CONCAT(i.name SEPARATOR ',') AS item from shelf AS s LEFT JOIN list AS l ON (l.sID = s.sID) LEFT JOIN item AS i ON (l.iID = i.iID) GROUP BY s.name;
with results:
+---------+------------------------+
| name | item |
+---------+------------------------+
| shelf 1 | cup,plate,book,flowers |
It's close, but again, I'd rather have the item's in their own columns.
Thanks for any advice!
J
__________________
I do stuff - aIm CS_Jay_D
|