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.

I can't as I need the parent id 18 and all the child of 21 but depending on the type id — Raj
– Raj, Commented Sep 27, 2014 at 11:39

Gordon Linoff · Accepted Answer · 2014-09-27 14:05:42Z

1

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.

edited Sep 27, 2014 at 14:05

answered Sep 27, 2014 at 11:30

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Raj Over a year ago

Thanks Gordon but I am getting all the ids now no matter which child page I am on. Isn't there any way to get the ids 19,20,22 and 23.

Gordon Linoff Over a year ago

@Raj . . . That is what this query returns. Look at the SQL Fiddle.

Raj Over a year ago

When I run sql it displays nothing in the fiddle..Also I tried it in my local phpmyadmin but it is same as before.Might be something is wrong from my end.

Gordon Linoff Over a year ago

@Raj . . . When I run the code in SQL Fiddle, it definitely returns what you want. I do notice that SQL Fiddle did not include the code on the right-hand side before. This seems to happen occasionally.

Raj Over a year ago

Sorry my mistake. The query works great. Thanks. Just wondering if there is any way to pass the parent id 18 and then it will find and get me 19 20 22 23 only.

|

Hatem · Accepted Answer · 2014-09-27 14:48:16Z

0

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

answered Sep 27, 2014 at 14:48

Hatem

3692 silver badges12 bronze badges

Collectives™ on Stack Overflow

Issue with mysql join query

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related