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

It doesn't help if the eyeballs do not look. Good luck.

:Oh crap:Oh crap

Right off the top of my head I have to say that the database design looks overly complicated. No need for 3 tables here and there's no value in it either.

Instead do this:

select * from item;
+-----+---------+
| iID | name | sID
+-----+---------+
| 1 | cup | 1
| 2 | plate | 1
| 3 | book | 1
| 4 | flowers | 1
+-----+---------+

select * from shelf;
+-----+---------+
| sID | name |
+-----+---------+
| 1 | shelf 1 |
+-----+---------+

As for getting the exact output you're looking for, the only way I can think of to do it is to construct a temp table adding columns for each of the items. I don't use MYSQL much, mostly SQL Server so there might be something I don't know about in there.

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 :2 cents:

Well there ya go then. Varius FTW.

Not true unless he will only ever have 1 of each item on the "shelf".. If there can be more than one then he needs the third table to do it right.

Third table "list" doesn't need nor should it have the ID field though. Should have a unique index though on sID,iID

very often Varius is teh winnar

The idea here is that I can add unlimited number of items on my shelf.

The "list" has a unique idea because it's a force of habit, and makes it easier when I have to destroy that connection.

I think I'm leaning towards the stored procedure, then dump into a storage table for fast display.

Thanks all for giving some ideas out!

J

just setup a normal table structure, and do a standard JOIN, then format it in php any way you want it...

or is there actually a good reason why you need some convoluted query like that?