How can I sort data before grouping in MYSQL
I have a big mysql query which needs to trawl through 4 tables to get all the 'items' for my application. Each item can have many categories and each user can have up to one of each item. The items and categories are easy:
SELECT Items.itemId, Items.name, Items.type, Categories.name AS category
FROM Items
LEFT JOIN ItemCategories ON Items.itemId = ItemCategories.itemId
LEFT JOIN Categories ON ItemCategories.categoryId = Categories.categoryId;
This produces most of the data I want. However I also need to know if each item is owned by a particular user. So I simply added another join and a boolean column:
SELECT Items.itemId, Items.name, Items.type, Categories.name AS category,
UserItems.userId = 7654321 AS userHas FROM Items
LEFT JOIN ItemCategories ON Items.itemId = ItemCategories.itemId
LEFT JOIN Categories ON ItemCategories.categoryId = Categories.categoryId
LEFT JOIN UserItems ON Items.itemId = UserItems.itemId;
The problem with this is that it will produce a LOT of unwanted results. For example if I have 500 users and each has 50 items then there would be 25,000 rows. Since there are only about 100 items in the database and each has approximately 3 categories I only really need about 300 rows. Basically I don't need to know about items that other users might have, only the particular user I'm interested in.
My next step was to try grouping the rows:
GROUP BY Items.itemId, Categories.name
However this doesn't ensure that my user's data in the userHas column is preserved. I tried to SORT BY userHas DESC, but that seems to be applied after the GROUP BY.
I have a feeling that the solution might involve one of these functions: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html but I'm stuck as to what or how. I am aware that it might not even be possible in one query.
Here's some output (not using GROUP BY):
+--------+--------+-----------+----------+---------+
| itemId | name | type | category | userHas |
+--------+--------+-----------+----------+---------+
| 1 | Llama | character | animal | NULL |
| 1 | Llama | character | nice | NULL |
| 2 | Slug | character | animal | 0 |
| 2 | Slug | character | animal | 1 |
| 2 | Slug | character | nasty | 0 |
| 2 | Slug | character | nasty | 1 |
| 3 | Sloth | character | animal | 1 |
| 3 | Sloth | character | animal | 0 |
| 4 | Banana | character | fruit | 0 |
| 4 | Banana | character | animal | 0 |
+--------+--------+-----------+----------+---------+
I want each item id, name, type all the categories and whether the user has one. The same data set With GROUP BY Items.itemId, Categories.name looks like:
+--------+--------+-----------+----------+---------+
| itemId | name | type | category | userHas |
+------开发者_如何学Python--+--------+-----------+----------+---------+
| 1 | Llama | character | animal | NULL |
| 1 | Llama | character | nice | NULL |
| 2 | Slug | character | animal | 0 |
| 2 | Slug | character | nasty | 0 |
| 3 | Sloth | character | animal | 1 |
| 4 | Banana | character | animal | 0 |
| 4 | Banana | character | fruit | 0 |
+--------+--------+-----------+----------+---------+
The userHas = 1 field for Slug has been lost in the GROUP BY. I want to save it.
Update:
This will select all items and show if the user 7654321
has them.
SELECT Items.itemId, Items.name, Items.type, Categories.name AS category,
NOT(ISNULL(UserID)) AS userHas
FROM Items
LEFT JOIN
ItemCategories
ON Items.itemId = ItemCategories.itemId
LEFT JOIN
Categories
ON ItemCategories.categoryId = Categories.categoryId
LEFT JOIN
UserItems
ON UserItems.itemId = Items.itemID
AND UserItems.userId = 7654321
Have you tried to add UserHas
column to the GROUP BY
clause ?
精彩评论