Trying to select row from mysql then in same select see if value exists

Question

Hey guys i have a database table called page_names that holds the pages in a nav. Within that table I have parentid which is 0 when its a main nav, if it has a number then its a child of that parent.

I need to select all rows from the table ( where ispublished = 1 ) but then I need to have another identifier in the dataset called 'haschild' which will be set to either 0 or a number to signify that that nav page has children. I thought about doing this with arrays in the php but realized it would be better to have that set.

I have tried to join the same table to set the value and selecting a select statement but I can not seem to achieve what I need. This seems like an easy enough thing but I'm stuck. This is where I am and I know its not even close.

    SELECT p.* , coalesce( pp.parentid, 0 ) AS haschild
    FROM page_names p
    LEFT JOIN page_names pp ON p.id = pp.parentid
    WHERE p.ispublished =1
    AND pp.ispublished =1
    ORDER BY p.orderout

Angelo Saleh · Accepted Answer · 2013-09-05 16:59:46Z

1

Here I propose a select.

SELECT p.*,
(SELECT COUNT(id) FROM page_names WHERE parentid=p.id AND ispublished =1) haschild
FROM page_names p
WHERE p.ispublished =1
ORDER BY p.orderout

in the field haschild you have the number of children that the record has.

http://sqlfiddle.com/#!2/326ab/10

answered Sep 5, 2013 at 16:59

Angelo Saleh

6227 silver badges17 bronze badges

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

Comments

DiMono · Accepted Answer · 2013-09-05 16:41:58Z

0

You should be able to do this by counting how many children it has, and then if that number is greater than 0 then it has children. Something like this should work:

SELECT p.*, count(pp.id) AS children
FROM page_names p
LEFT JOIN page_names pp ON p.id = pp.parentid
WHERE p.ispublished = 1
AND pp.ispublished = 1
GROUP BY p.id
ORDER BY p.orderout

answered Sep 5, 2013 at 16:41

DiMono

3,3762 gold badges21 silver badges41 bronze badges

1 Comment

Webtect Over a year ago

Hi @DiMono, thanks for replying. What you posted only returns the ones with a child, I need all of the pages and then another column called haschild that has either 1 or a number to show if it has children.

Collectives™ on Stack Overflow

Trying to select row from mysql then in same select see if value exists

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related