CS-Jay

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

fatfoo

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

__________________
Send me an email: [email protected]

munki

__________________

WarChild

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 |
+-----+---------+

__________________
.

WarChild

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.

__________________
.

Varius

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

__________________
Skype variuscr - Email varius AT gmail

WarChild

Well there ya go then. Varius FTW.

__________________
.

Tempest

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

myneid

very often Varius is teh winnar

__________________
Tanguy 0x7a69 inc. Programmer/President/CEO
http://www.0x7a69.com
A Leader in Programming since 1996
PHP, Ruby on Rails, MySQL, PCI DSS, and any Technical Consulting

CS-Jay

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

__________________
I do stuff - aIm CS_Jay_D

woj

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?

__________________
Custom Software Development, email: woj#at#wojfun#.#com to discuss details or skype: wojl2000 or gchat: wojfun or telegram: wojl2000
Affiliate program tools: Hosted Galleries Manager Banner Manager Video Manager
Wordpress Affiliate Plugin Pic/Movie of the Day Fansign Generator Zip Manager