nielsv nielsv - 1 year ago 120
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:

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 Source

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 (

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

Or using Joins

FROM category c
    LEFT JOIN category pc
    ON = pc.parent_category_id
    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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download