CODEWITHSUNDEEP
sql
Ivo
Ivo

Reputation: 363

How to select only children in table

I have the following table taxonomy which looks like:

taxonomy_id  | parent_id
------------------------
1            | 0
2            | 0
3            | 1
4            | 1
5            | 2
6            | 3

I want to select all items which are not parents, in the case 4,5 and 6. The first 3 items obviously are parents and I don't need them. What should be the query?

Upvotes: 1

Views: 179

Answers (5)

TorchTT
TorchTT

Reputation: 132

Alternatively:

select * from taxonomy where taxonomy_id <> all (select parent_id from taxonomy)

Demonstration

Thanks for the link to the user Tim Schmelter excellent service sqlfiddle.com

Upvotes: 0

Ahmad Alkhawaja
Ahmad Alkhawaja

Reputation: 539

Try this

SELECT * FROM taxonomy WHERE taxonomy_id NOT IN (SELECT parent_id FROM taxonomy WHERE parent_id <> 0)

Upvotes: 0

Denis de Bernardy
Denis de Bernardy

Reputation: 78443

In MySQL you'll need to resort to a correlated subquery for this.

Either not exists:

select t.*
from taxonomy t
where t.parent_id <> 0
and not exists (select 1 from taxonomy t2 where t2.parent_id <> t.taxonomy_id)

Or not in:

select t.*
from taxonomy t
where t.parent_id <> 0
and t.taxonomy_id not in (select t2.parent_id from taxonomy t2)

Upvotes: 0

juergen d
juergen d

Reputation: 204756

select taxonomy_id
from taxonomy
where taxonomy_id not in
(
  select distinct parent_id from taxonomy
)

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460108

Use NOT EXISTS

SELECT taxonomy_id, parent_id
FROM dbo.Taxonomy t1
WHERE NOT EXISTS(
    SELECT 1 FROM dbo.Taxonomy t2
    WHERE t1.taxonomy_id=t2.parent_id
)

Demonstration

At least in SQL-Server this is the best approach: Should I use NOT IN, OUTER APPLY, LEFT OUTER JOIN, EXCEPT, or NOT EXISTS?

Upvotes: 3

Related Questions