CODEWITHSUNDEEP
sqlms-accessself-join
Nelluk
Nelluk

Reputation: 1241

Select parents that have no children in a self-join relationship

I have a simple table of category names that relates to itself with a category_parent = id. I need to select records that have no children.

From other answers I have cobbled together this statement:

SELECT cat.category_name AS 'Child Name', cat.id AS 'Child ID',
cat.category_parent AS 'Childs Parent', cat_par.category_name AS 'Parent Name', 
cat_par.id AS 'Parent ID'
FROM category AS cat
LEFT JOIN category AS cat_par ON cat.category_parent = cat_par.id
WHERE cat_par.id IS NULL;

This will successfully select records that have no PARENT. I tried changing the last clause to WHERE cat.category_parent IS NULL but that yielded an empty yet.

I have also tried this statement based on another answer:

SELECT cat.category_name AS 'Child Name', cat.id AS 'Child ID', 
cat.category_parent AS 'Childs Parent' cat_par.category_name AS 'Parent Name', 
cat_par.id AS 'Parent ID'
FROM category AS cat
WHERE NOT EXISTS 
(SELECT id FROM category AS cat_par WHERE cat.category_parent = cat_par.id);

Which returns the error No value given for one or more required parameters.

Upvotes: 2

Views: 2470

Answers (5)

Fellipe Sanches
Fellipe Sanches

Reputation: 8135

-- get parents categories without children

SELECT id, category_name
FROM category
WHERE id NOT IN 
(
    SELECT c.id
    FROM category c, category p
    WHERE c.parent_id = p.id
) 
AND id NOT IN 
(
    SELECT p.id
    FROM category c, category p
    WHERE c.parent_id = p.id
)

EXPLANATION: The logic here is very simple. With the query below (used inside both NOT IN) we get parent categories and its children:

SELECT c.id, p.id
FROM category c, category p
WHERE c.parent_id = p.id

As NOT IN only allows comparison with one column, we use it twice:

Once to get the inverse of the parents, another to get the inverse of the children. What's left are "parents" without children.

Upvotes: 0

fthiella
fthiella

Reputation: 49049

If you just need the id and the category name of records with no children this also works:

SELECT
  category.id,
  category.category_name
FROM
  category LEFT JOIN category AS category_1
    ON category.id = category_1.category_parent
WHERE
  category_1.category_parent IS NULL

and i think this query looks nice. But if you also need the parent's name, you can use this:

SELECT
  category.id,
  category.category_name,
  category_1.category_name
FROM
  (category LEFT JOIN category AS category_2
    ON category.id = category_2.category_parent)
  LEFT JOIN category AS category_1
    ON category.category_parent = category_1.ID
WHERE
  category_2.category_parent Is Null;

and yes, it gets a little more complicated: it's just a nice sql exercise but i would prefer to use the Exists version instead.

Upvotes: 1

Tobsey
Tobsey

Reputation: 3400

The problem with the second query is that you are trying to include cat_par.category_name in your results set, even though it's not in your FROM clause. You cannot return a column from the tables in your EXISTS subquery.

YOu want the following:

SELECT 
    cat.category_name AS 'Child Name', 
    cat.id AS 'Child ID', 
    cat.category_parent AS 'Childs Parent',
    cat_par.category_name AS 'Parent Name',
    cat_par.id AS 'Parent ID'
FROM 
    category AS cat
    LEFT JOIN category AS cat_par ON cat.category_parent = cat_par.id
WHERE 
    NOT EXISTS 
    (SELECT 
        NULL 
    FROM 
        category AS cat_childless 
    WHERE cat.id = cat_childless.category_parent);

Upvotes: 0

Laurence
Laurence

Reputation: 10976

Select p.id, p.category_name
From category As p
Where Not Exists (
  Select 'x' 
  From Category c 
  Where c.category_parent = p.id
)

Upvotes: 3

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

I think I understand the relation enough. How about this?

SELECT cat.*
FROM category cat
WHERE cat.id not in (
   SELECT distinct category_parent FROM category
);

Upvotes: 2

Related Questions