View Single Post
Old 12-22-2010, 08:46 AM  
CS-Jay
Confirmed User
 
CS-Jay's Avatar
 
Join Date: Oct 2003
Location: Command Central, West Palm Beach, Fl
Posts: 1,794
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
CS-Jay is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote