MYSQL - SELECT products WHERE parent_category is active

Question

assuming we have a database category structure like this

# Categories Table
| category_id | parent_category_id | status |

# Products Table
| product_id | category_id |

how do i get the list of products in a category only if the parent_category_id has an active status = 1 ?

i guess i could use some sub-query in the SELECT statement, but i don't know how! :(

something like:

SELECT p.*, (SELECT * FROM ? WHERE ? ) AS x 
FROM products AS p 
    LEFT JOIN categories AS c ON p.category_id = c.category_id 
WHERE ... 
AND p.product_id = '?' 
AND ...

Thank you in advance for any advice!

NB: i'm using PHP as backend language, just in case i need some sort of data manipulation to pass to the mysql query.

Not sure whats the reason behide `WHERE 1=1` ? Because it's always true or 1 for the matter.. Also using filtering on a left table which is not a `IS NULL` check will cause the `LEFT JOIN` to be filterd as a `INNER JOIN` instead.. Can't say if that's a problem here because off missing SQL code.. — Raymond Nijland, Feb 25 '19 at 15:23
Check here https://stackoverflow.com/questions/242822/why-would-someone-use-where-1-1-and-conditions-in-a-sql-clause — Juan Carlos Oropeza, Feb 25 '19 at 15:25
@Raymond Nijland: 1=1 just a dummy segment of text, i have just replaced with ...! thanks. — Marco, Feb 26 '19 at 07:42

score 0 · Answer 1 · edited Feb 25 '19 at 15:08

0

try not to use subquery, because it can affect the speed of loading data

maybe it can help

SELECT *
FROM categories c
INNER JOIN products p ON p.category_id=c.category_id
WHERE c.status = 1

edited Feb 25 '19 at 15:08

Juan Carlos Oropeza

47,252
12
78
118

answered Feb 25 '19 at 15:08

Hendri Faisal Hidayat

61
4

To format as `code`, select the text and press `ctrl-k` or add 4 spaces before each line – Juan Carlos Oropeza Feb 25 '19 at 15:08

blupointmedia · Answer 2 · 2019-02-25T15:10:53.253

0

SELECT p.* FROM products p
INNER JOIN categories child ON child.category_id = p.category_id
INNER JOIN categories parent ON parent.category_id = child.parent_category_id
WHERE parent.status=1

edited Feb 25 '19 at 15:10

answered Feb 25 '19 at 15:08

blupointmedia

564
2
10

If you use `WHERE`, `LEFT JOIN` become `INNER JOIN` – Juan Carlos Oropeza Feb 25 '19 at 15:09

score 0 · Answer 3 · answered Feb 25 '19 at 15:09

0

Here a example query, I don't use left join or join, becasue you require that the record exists in order to get the status.

SELECT 
   *
FROM
   products, categories
WHERE
   products.category_id = categories.category_id
   AND status = '1'

answered Feb 25 '19 at 15:09

Vidal

2,605
2
16
32

that's your preference. https://dba.stackexchange.com/questions/3480/what-is-more-efficient-a-where-clause-or-a-join-with-million-plus-row-tables – Vidal Feb 25 '19 at 15:46

score 0 · Answer 4 · answered Feb 25 '19 at 15:23

First you have to get all active categories

SELECT child.*
FROM Categories child
JOIN Categories parent
  ON child.parent_category_id = parent.category_id 
 AND parent.status = 1

But you also need the root categories with not parent_id

SELECT *
FROM Categories
WHERE parent_category_id IS NULL
  AND status = 1

So you UNION both

SELECT *
FROM Categories child
JOIN Categories parent
  ON child.parent_category_id = parent.category_id 
 AND parent.status = 1

UNION ALL

SELECT *
FROM Categories
WHERE parent_category_id IS NULL
  AND status = 1

Now you get the products for those categories

SELECT *
FROM (  ... UNION ALL ... ) active_categories
JOIN Products
 ON active_categories.category_id = Product.category_id

MYSQL - SELECT products WHERE parent_category is active

4 Answers4