CODEWITHSUNDEEP
mysqlsqlsearch
Gatusko
Gatusko

Reputation: 2598

How to see if a Parent have a Children in mysql?

How to check if a Parent have a Child in a table of mysql? I was just checking a database and use a recursivity for doing this thing because all the data is in the same table. But it has a lot of data like 100k of register. And is not doing recursive search in mysql is doing in the code (Java), so the software is very slow. 

id  | id_parent         
1         null              
2         1           
3         2
4         null
5         3

Now I need the get the last child!

 id      | id_parent          
  5           3

Or get if the id have children for doing in the code and doing multiple queries.

Example:

   if(i.id have child)
   {
    then do a search of the child
   }    
   else
   return the same valor

Some help please. The recursive search is a slowpoke. It take 10 minutes for searching only 2 records from the tables. And I can't change the database or the tables.

----EDIT 2---- Thanks to all answers, now I have the found de orig source. A

  id      | id_orig          
  5           1

Im doing this via code, thanks to all. My solution was based in your answers

Upvotes: 1

Views: 3286

Answers (2)

flaschenpost
flaschenpost

Reputation: 2235

You need only the list of last children? That is easy:

select child.id, child.id_parent
from people as child
left join people as no_child on no_child.id_parent = child.id
where no_child.id is null;

Recursion in general is hard in SQL, but a few things can be done without recursion.

Another option: if you really have just a few 100k entries, just try to keep a good data structure in java synchronized with the database. This data structure would be very fast searchable without firing requests to the database. This of course is just valid if only one application from one server will write to that table and if the writes are not too often.

Another option: make an extra column 'last_child' to the table, and be careful to update that on every write request. Since this needs no recursion, you can go back to normal SQL for this. It depends if you have mass data changes for child-parent-relations, if you need to delete entries and so on, but if you want to go this way we could write some update statements.

DELETE

Upvotes: 3

AgeDeO
AgeDeO

Reputation: 3157

Assuming that the table is called people

To get all id's that have a parent you can do the following:

SELECT id, id_parent FROM people WHERE id_parent NOT NULL

To recieve the data that the parent has INNER JOIN the parent

SELECT c.id, c.id_parent, p.id, p.id_parent FROM people as c
INNER JOIN people as p ON (c.id_parent = p.id)

The above query can be extended with more JOINS when it is required to go deeper. But mySQL is not recursive so you will always have to specify how deep you want to go.

INNER JOIN people as p ON (c.id_parent = p.id)
INNER JOIN people as p2 ON (p.id_parent = p2.id)

When you always want result whetever the child has a parent or not, change INNER JOIN by LEFT OUTER JOIN

Extending on Pradeep's comment: it is also possible to count the amount of children per parent.

SELECT COUNT(*) FROM people GROUP BY id_parent

Adding HAVING COUNT(*) > 0 at the end of the query only shows parent's with children

Upvotes: 1

Related Questions