CODEWITHSUNDEEP
mysql
MrCarrot
MrCarrot

Reputation: 2768

MySQL query with condition based on field value OR left join value

In MySQL I have 3 tables called "product", "product_group" and "product_category"

Products can be related to each other via the "product_group" table.

Products can appear in particular categories via the "product_category" table.

What I want to do is retrieve all products from a particular category, that either have a price, or contain one or more child products that have a price.

Here's an example of the tables:

product
=======
product_id      price
1               10.00
2               20.00
3               0.00
4               40.00
5               50.00
6               0.00
7               0.00
8               0.00
9               0.00
10              0.00
11              1.99

product_group
=============
product_id      child_product_id
6               1
6               2
7               3
7               4
8               9

product_category
================
category_id     product_id
1               5
1               6
1               7
1               8
1               10
2               11

This means that products 1 and 2 are children of product 6. Products 3 and 4 are children of product 7. Product 9 is a child of product 8.

Products 5, 6, 7, 8 and 10 are in category 1.

What I want is a query to return all products in category 1 that either have a price, or contain one or more children that have a price. So I would expect to get products 5, 6, 7. Although products 8 and 10 are in category 1 I don't want these as they don't have a price, and they don't contain children that have a price.

I could post the query I have written so far using a combination of inner and left joins, but I fear that it's completely wrong.

Thank you for looking.

* Edit *

I discovered my original query did work, I'd got the data in two of my columns mixed up. Here is my original query below:

SELECT p.product_id 
  FROM product p 
  JOIN product_category c 
    ON c.product_id = p.product_id
   AND c.category_id = 1 
  LEFT
  JOIN product_group g 
    ON p.product_id = g.product_id 
  LEFT
  JOIN product AS p2 
    ON p2.product_id = g.child_product_id
   AND p2.price > 0
 WHERE p.price > 0 OR p2.product_id IS NOT NULL 
 GROUP BY p.product_id

Upvotes: 1

Views: 2066

Answers (1)

spencer7593
spencer7593

Reputation: 108410

I'd do it something like this:

SELECT p.product_id
  FROM product p
  JOIN product_category c
    ON c.product_id = p.product_id
   AND c.category_id = 1
  LEFT
  JOIN ( SELECT g.product_id
           FROM product_group g
           JOIN product h
             ON h.product_id = g.child_product_id
            AND h.price > 0.00
          GROUP BY g.product_id
       ) r
    ON r.product_id = p.product_id
 WHERE p.price > 0 
    OR r.product_id IS NOT NULL

NOTES:

The JOIN to product_category gets us the restriction that the product has to be in category 1.

The inline view (derived table aliased as r) gets us a list of "parent" product_id that have have child_product_id which have a price.

We make that an OUTER join so we still get all the rows in category 1, we're just doing a match.

In the WHERE clause, we're filtering out rows, and keeping only those product that have a price, or that had a matching row from the inline view.

This is just one way, there are there are several ways to get an equivalent resultset.

This assumes that product_id is unique in the product table.

This also assumes that the (category_id,product_id) tuple is unique in the product_category table, that is, there won't be duplicates, e.g. (1,5),(1,5). If that assumption isn't valid, then we probably want to add a GROUP BY p.product_id at the end of the query, to get a distinct list.

Also, this only looks at direct children; it doesn't look to see if a grandchild (a child of the child) have a price.

Upvotes: 1

Related Questions