Issue with mysql join query

Question

I am having a problem with a mysql query. I want to have multilevel child ids of a parent. So if the parent id is 18 then I want to have child ids of 18 and if there is another level of any child then I want them as well based on type id

My Table structure is like below -- pages Table

id      title             parent_id      type_id

18      About Us          0              4
19      Child 1           18             6
20      child 2           18             7
21      child 3           18             1
22      sub child 1       21             14
23      sub child 2       21             14
24      sub child 3       21             15

Second Table -- page_types

id          typetitle

6           one column
7           two column
14          slider

My desired output should be

id      title             parent_id      type_id        typetitle

19      Child 1           18             6              one column
20      child 2           18             7              two column
22      sub child 1       21             14             slider
23      sub child 2       21             14             slider

My query is

SELECT 
   p.id AS childid,
   p.title AS ptitle,
   pt.typetitle as page_type_title
   FROM pages AS p,page_types as pt
   where p.type_id in (6,7,14) and p.parent_id=18 and p.type_id = pt.id

But I am getting only 2 rows( 19 and 20) but not 22 and 23.

Answer 1

I thinks this is what you search about it :

SELECT * FROM pages AS p INNER JOIN types AS t ON p.type_id = t.id WHERE p.type_id in (6,7,14) and p.parent_id <> 0 

Answer 2

You seem to want the leafs of the tree but not the intermediate nodes. Try doing this:

SELECT p.id AS childid, p.title as ptitle, pt.typetitle as page_type_title
FROM pages p join
     page_types pt
     ON p.type_id = pt.id
WHERE p.type_id in (6, 7, 14) and
      NOT EXISTS (SELECT 1 FROM pages p2 WHERE p2.parent_id = p.id);

Here is a SQL Fiddle showing the query in action.