Subqueries in MySQL creating duplicate results

Question

I am having a bit of trouble with my query.

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 are you trying to get back data wise here? Could it not be achieved with a single query and a join? — dougajmcdonald
– dougajmcdonald, Commented Sep 8, 2011 at 15:14

Conrad Frix · Accepted Answer · 2011-09-08 15:15:27Z

2

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

answered Sep 8, 2011 at 15:15

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Metalmini Over a year ago

Thanks for the explenation about the cross join!

Metalmini Over a year ago

But i dont reallu understand what you are doing in your query. How do i join two tables when there are no ids that match? Also i am creating this crazy query because i want to have id from two types of data (category_id, product_id)

Conrad Frix Over a year ago

I was making a guess at what you really want and was wrong. You'll need to do as others have asked which explain what you really want. Preferably by editing your question to include the output you want.

glglgl · Accepted Answer · 2011-09-08 15:15:37Z

0

It is just a cross product of two temporary tables representing the respective result sets of the subqueries.

answered Sep 8, 2011 at 15:15

glglgl

91.5k13 gold badges157 silver badges230 bronze badges

Collectives™ on Stack Overflow

Subqueries in MySQL creating duplicate results

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related