开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜