Catch Fatherless Children in a MYSQL hierarchical structure

Asked

Viewed 336 times

2

I have a table with the following structure

|id|parent_id|name|

At some point, due to a programming error, some parents were erased and their children remained.

So I need to create a query to fetch all these children.

I tested this query:

SELECT filha.id, filha.name FROM filha WHERE (SELECT pai.id, pai.name FROM pai WHERE pai.id = filha.pai_id) = NULL

But my structure is on the same table.

mysql
  • 1

    What in this table says that the item is child ? what is the item parent? vague in this respect? is a relationship self, but, how does it work in your case

  • 1

    Just complementing the questions of Virgilio, give an example of what has in your table and with this data what you want to have return

3 answers

3


First of all it would be enough to put a foreign key constraint on your table that such an error would not occur.

Try

SELECT id_parent, id, name FROM sua_tabela a
    WHERE NOT EXISTS(SELECT 1 FROM sua_tabela b WHERE b.id = a.parent_id);

  • Perfect! Just like that.

  • Thank you, you helped me a lot!

  • If you solved the problem you can accept his answer as shown in the image: https://i.stack.Imgur.com/eyLkG.jpg

3

You can check all the records you have parent_id that is not on the same table using NOT IN.

Heed

You said it’s the same table, but you’re putting different names in the query, pai and filha.

Considering it’s the same table and her name is paito query that’s the one:

SELECT id, name FROM pai WHERE parent_id NOT IN (SELECT id FROM pai);

If it’s another table a query is

SELECT id, name FROM filha WHERE parent_id NOT IN (SELECT id FROM pai);

2

Being the same table, if there was the foreign key of the relationship, the exclusion would not be possible.

As there was the exclusion, I understand that the codes of the "parent records" are still in the "children records", so try this:

SELECT filha.id, filha.name 
  FROM tabela filha 
 WHERE not exists (SELECT pai.id FROM tabela pai WHERE pai.id = filha.parent_id)

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.