Subqueries in MySQL creating duplicate results
I am having a bit of trouble with my quer开发者_JAVA技巧y.
As you can see i am running two queries. This all looks very well and mysql takes it like a man. But the result that i get is 5 times the same stuff.
SELECT s.category_id, p.product_id
FROM (
SELECT ros_categories.category_id
FROM ros_categories, ros_variantIndex
WHERE ros_categories.name = ros_variantIndex.variantText
AND ros_categories.group = 'Sizes'
LIMIT 0 , 5
) s, (
SELECT ros_product.product_id
FROM ros_product, ros_variantIndex
WHERE ros_product.vart = ros_variantIndex.vart
LIMIT 0 , 5
) p
Output:
+-------------+------------+
| category_id | product_id |
+-------------+------------+
| 110 | 1 |
| 7 | 1 |
| 8 | 1 |
| 9 | 1 |
| 10 | 1 |
| 110 | 1 |
| 7 | 1 |
| 8 | 1 |
| 9 | 1 |
| 10 | 1 |
| 110 | 1 |
| 7 | 1 |
| 8 | 1 |
| 9 | 1 |
| 10 | 1 |
| 110 | 1 |
| 7 | 1 |
| 8 | 1 |
| 9 | 1 |
| 10 | 1 |
| 110 | 1 |
| 7 | 1 |
| 8 | 1 |
| 9 | 1 |
| 10 | 1 |
+-------------+------------+
25 rows in set (0.01 sec)
What is going on here? Is this my problem or is mysql being strange?
EDIT:
Thanks for explaining me what the problem was. I fixed it using several joins. So thanks for pointing out my error and naming the problem :-) And sorry bout the silly question
What is going on here? Is this my problem or is mysql being strange?
What you've done is create a Cartesian product also known as a Cross Join. typically you just join s and p to get what you want but the JOIN criteria isn't clear.
Perhaps you want this (guessing at columns on your tables)
SELECT s.category_id, p.product_id
FROM (
SELECT ros_categories.category_id
FROM ros_categories, ros_variantIndex
WHERE ros_categories.name = ros_variantIndex.variantText
AND ros_categories.group = 'Sizes'
LIMIT 0 , 5
)s
INNER JOIN (
SELECT ros_product.product_id, ros_product.category_id
FROM ros_product, ros_variantIndex
WHERE ros_product.vart = ros_variantIndex.vart
LIMIT 0 , 5
)p
on s.category_id = p.category_id
It is just a cross product of two temporary tables representing the respective result sets of the subqueries.
精彩评论