CODEWITHSUNDEEP
mysqljoinparent-child
mpsbhat
mpsbhat

Reputation: 2773

Parent child relation using mysql

I have a sample mysql table named relation as follows:

ID  TypeID  Name         Parent_ID
----------------------------------
1    1      Parent        0
2    2      Child1        1
3    2      Child2        1
4    3      GrandChild1   2
5    3      GrandChild2   2
6    3      GrandChild3   2
7    3      GrandChild4   3
8    3      GrandChild5   3

How can I generate a report like given below using mysql queries.

ParentName  ChildName   GrandChildName
--------------------------------------
Parent      Child1      GrandChild1
Parent      Child1      GrandChild2
Parent      Child1      GrandChild3
Parent      Child2      GrandChild4
Parent      Child2      GrandChild5

Upvotes: 1

Views: 99

Answers (2)

1000111
1000111

Reputation: 13519

If you are worried about the performance of this query then you can check the performance running the above query preceding the word "EXPLAIN".

    EXPLAIN select 
      rl.Name as 'ParentName', 
      rl1.Name as 'ChildName',
      rl2.Name as 'GrandChildName'  from relation rl
    INNER JOIN (select * from relation where TypeID=2) rl1 ON rl.ID=rl1.Parent_ID
    INNER JOIN (select * from relation where TypeID=3) rl2 ON rl1.ID=rl2.Parent_ID

If you see that performance is not quite good then you can create an index On TypeID.

Here is a link on mysql Explain :

http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/

CREATE INDEX Idx_TypeID relation(TypeID);

And change the above query like below :

select rl.Name as 'ParentName', rl1.Name as 'ChildName',rl2.Name as 'GrandChildName'  
from relation rl
INNER JOIN (select * from relation where TypeID=2) rl1 ON rl.ID=rl1.Parent_ID
INNER JOIN (select * from relation where TypeID=3) rl2 ON rl1.ID=rl2.Parent_ID
WHERE rl.TypeID=1

Note : I've added a where clause in the last line for making use of the index (Idx_TypeID) just created. (performance gets better.)

Now check this query again preceding the word "EXPLAIN". This time you will get a better explain output. Good day!

EDIT :

The sub-queries can be avoided. Thanks to mpsbhat for pointing out an important point what I missed out earlier. So here is the query I've directly quoted from mpsbhat's post:

SELECT
  rl. NAME AS 'ParentName',
  rl1. NAME AS 'ChildName',
  rl2. NAME AS 'GrandChildName'
FROM
  relation rl
INNER JOIN relation rl1 ON rl.ID = rl1.Parent_ID
AND rl1.TypeID = 2
INNER JOIN relation rl2 ON rl1.ID = rl2.Parent_ID
AND rl2.TypeID = 3

If you explain the query it shows better performance than the previous.

Upvotes: 1

mpsbhat
mpsbhat

Reputation: 2773

It can be achieved by 

select rl.Name as 'ParentName', rl1.Name as 'ChildName',rl2.Name as 'GrandChildName'  from relation rl
INNER JOIN (select * from relation where TypeID=2) rl1 ON rl.ID=rl1.Parent_ID
INNER JOIN (select * from relation where TypeID=3) rl2 ON rl1.ID=rl2.Parent_ID

I like to know any other methods.

EDIT: Here is another method that I found without inner query as given below which is I really wanted to achieve.

select rl.Name as 'ParentName', rl1.Name as 'ChildName',rl2.Name as 'GrandChildName'  from relation rl
INNER JOIN relation rl1 ON rl.ID=rl1.Parent_ID and rl1.TypeID=2
INNER JOIN relation rl2 ON rl1.ID=rl2.Parent_ID and rl2.TypeID=3

Upvotes: 1

Related Questions