user3733831 user3733831 - 2 months ago 5
MySQL Question

What is the issue in this Mysql query

I am having a mysql query problem in my project.

There are 3 tables named product, product_option, product_category

This is data for product_option:

mysql> select * from product_option where product_id = 2;
+-------------------+------------+
| product_option_id | product_id |
+-------------------+------------+
| 2 | 2 |
| 3 | 2 |
| 4 | 2 |
+-------------------+------------+
3 rows in set (0.00 sec)


This is data for product_category:

mysql> select * from product_category where product_id = 2;
+-------------+------------+
| category_id | product_id |
+-------------+------------+
| 15 | 2 |
| 2 | 2 |
| 4 | 2 |
+-------------+------------+
3 rows in set (0.00 sec)


Now I want to select product with these options and category.

This is how I tried it. But its giving 9 rows. (its duplicating category_id and option_id 3 times)

mysql> SELECT p.product_id
-> , p.product_name
-> , p.enabled
-> , p.date_available
-> , pc.category_id
-> , po.product_option_id
-> FROM product p
-> INNER JOIN `product_category` pc USING(product_id)
-> LEFT JOIN `product_option` po USING(product_id)
-> WHERE p.product_id = 2;
+------------+--------------+---------+----------------+-------------+-------------------+
| product_id | product_name | enabled | date_available | category_id | product_option_id |
+------------+--------------+---------+----------------+-------------+-------------------+
| 2 | dsfad | 1 | 2016-10-18 | 15 | 2 |
| 2 | dsfad | 1 | 2016-10-18 | 2 | 2 |
| 2 | dsfad | 1 | 2016-10-18 | 4 | 2 |
| 2 | dsfad | 1 | 2016-10-18 | 15 | 3 |
| 2 | dsfad | 1 | 2016-10-18 | 2 | 3 |
| 2 | dsfad | 1 | 2016-10-18 | 4 | 3 |
| 2 | dsfad | 1 | 2016-10-18 | 15 | 4 |
| 2 | dsfad | 1 | 2016-10-18 | 2 | 4 |
| 2 | dsfad | 1 | 2016-10-18 | 4 | 4 |
+------------+--------------+---------+----------------+-------------+-------------------+
9 rows in set (0.00 sec)


I also tried it something like this:

SELECT p.product_id
, p.product_name
, p.description
, p.meta_title
, p.meta_description
, p.meta_key_word
, p.brand
, p.quantity
, p.price
, p.enabled
, p.date_available
, pc.category_id
, po.product_option_id
FROM product p
INNER JOIN (
SELECT product_id, category_id
FROM product_category
GROUP BY product_id
) pc ON pc.product_id = p.product_id
LEFT JOIN (
SELECT product_id, product_option_id
FROM product_option
GROUP BY product_id
) po ON po.product_id = p.product_id
WHERE p.product_id = 2\G


But neither works for me.
can anybody tell me what would be this issue?

Thank you.

Answer

Others have answered that you're getting a Cartesian product of product options and product categories. That's the answer to your question "what is the issue with this query?"

You probably also want to know how to change the query so that it does not return a Cartesian product. I can think of two solutions:

  1. Execute one query for each join, and don't try to combine them into one query. You will get two result sets, which you then must fetch in your application code and combine into whatever data structure you want to represent one "product" object.

    SELECT p.product_id
         , p.product_name
         , p.description
         , p.meta_title
         , p.meta_description
         , p.meta_key_word
         , p.brand
         , p.quantity
         , p.price
         , p.enabled
         , p.date_available
         , pc.category_id
    FROM  product p
    INNER JOIN (
              SELECT product_id, category_id
              FROM   product_category
              GROUP  BY product_id
              ) pc ON pc.product_id = p.product_id
    WHERE p.product_id = 2
    
    SELECT p.product_id
         , po.product_option_id
    FROM  product p
    LEFT JOIN (
              SELECT product_id, product_option_id
              FROM   product_option
              GROUP  BY product_id
              ) po ON po.product_id = p.product_id
    WHERE p.product_id = 2
    

You may think, "I have to get all the data in one query, because I'm using a programming tool or framework that requires it!" This is not a good reason. Many domain objects require multiple SQL queries to fetch all their data. If you're using a framework that doesn't support real-world applications, then you should change the framework.

  1. Reduce the results from each joined table to one row, so that a Cartesian product is only multiplying 1x1 row.

    SELECT p.product_id
         , p.product_name
         , p.description
         , p.meta_title
         , p.meta_description
         , p.meta_key_word
         , p.brand
         , p.quantity
         , p.price
         , p.enabled
         , p.date_available
         , GROUP_CONCAT(pc.category_id) AS category_id_list
         , GROUP_CONCAT(po.product_option_id) AS product_option_id_list
    FROM  product p
    INNER JOIN (
              SELECT product_id, category_id
              FROM   product_category
              GROUP  BY product_id
              ) pc ON pc.product_id = p.product_id
    LEFT JOIN (
              SELECT product_id, product_option_id
              FROM   product_option
              GROUP  BY product_id
              ) po ON po.product_id = p.product_id
    WHERE p.product_id = 2
    GROUP BY p.product_id
    
    +------------+--------------+---------+----------------+-------------+-------------------+
    | product_id | product_name | enabled | date_available | category_id | product_option_id |
    +------------+--------------+---------+----------------+-------------+-------------------+
    |          2 | dsfad        |       1 | 2016-10-18     |      2,4,15 |             2,3,4 |
    +------------+--------------+---------+----------------+-------------+-------------------+
    

This solution returns the list of categories and product options as comma-separated strings, not as individual items, so your application code that fetches the results will have to split the string into its elements.

Comments