nielsv nielsv - 5 months ago 24
MySQL Question

SELECT child categories and parent categories with no child category

This is my table structure in my database:

enter image description here

Now I would like to select the following categories:


  • Child categories (categories where parent_category_id is NOT NULL)

  • Categories that don't have child categories (parent_category_id is NULL and no other category has parent_category_id equal to this category)



I've tried this query:

SELECT *
FROM category
WHERE parent_category_id IS NOT NULL
OR (id NOT IN (SELECT id FROM category WHERE parent_category_id IS NOT NULL) AND parent_category_id IS NULL)


In my database I have the following records:

enter image description here

But when I execute the query he returns ALL the records. And I don't want the record Category B Head. Because it is a parent category with child categories.

I want the following records back:


  • Category A

  • Category B - Child



How can I do this?

Answer

Here are a couple of ways you can try from a performance standpoint or just in case you prefer different code structures

Using EXISTS instead of IN (http://dev.mysql.com/doc/refman/5.7/en/exists-and-not-exists-subqueries.html)

SELECT * 
FROM category c
WHERE parent_category_id IS NOT NULL 
OR 
(
  parent_category_id IS NULL 
  AND NOT EXISTS (SELECT * FROM category pc WHERE pc.parent_category_id = c.id)
)

Or using Joins

SELECT DISTINCT c.*
FROM category c
    LEFT JOIN category pc
    ON c.id = pc.parent_category_id
WHERE
    c.parent_category_id IS NOT NULL 
    OR (c.parent_category_id IS NULL
       AND pc.parent_category_id IS NULL)

Exists and Joins are usually thought to be more efficient on performance but this could be a case where it wouldn't be just test and see what you like.

Comments