CODEWITHSUNDEEP
phpmysqlswitch-statement
Keith D Kaiser
Keith D Kaiser

Reputation: 1036

Parent/Child MySQL query with CASE/WHEN or switch

I’m using PHP to dynamically add another table to a page based on a parent/child relationship.

I’ve greatly simplified things so lets suppose I have a table 'FAMILY' with three columns Parent Int(6), Child varchar(15), and Fname varchar(25). The table has four rows, whose values are; Row 1 : 4, NULL , Bill Row 2: 5, 4, Tom Row 3: 6, NULL, Frank Row 4: 7, 4, Sam

As you can tell Tom and Frank are children of Bill. And so Bill is the parent of Tom and Sam.

Regardless of whose record I’m currently looking at, I would like to be able to dynamically see the other associated records as well. Forget the PHP part, I have that working. I just need the MySQL query.

The way I see it, I need one query that will do two different things depending on the value of child. 1: If Child is NULL (or blank) as in Row 1, I need it to return Row 2 and 4 based on the parent value of 4. In other words I’m looking at a Parent. 2) If Child is NOT NULL, as in Rows 2 & 4, I need it to return row 1 and the other row 2 or 4 depending which child I was looking at. Now I’m looking at a child record.

SELECT * FROM FAMILY WHERE
 CASE WHEN Child  IS NOT NULL then parent = 4
 WHEN child IS NULL then child = 4
 END
 GROUP BY PARENT

I’m pretty sure that I’m using CASE/WHEN incorrectly but after trying about a thousand things I give up and need to ask for help.

Please a MySQL query that can evaluate if it’s a parent or child and then pull the appropriate row(s) would be very much appreciated.

Thanks in advance.

Upvotes: 0

Views: 215

Answers (1)

Rahul
Rahul

Reputation: 77906

Not much sure from your question statement but you can modify your WHERE condition to be like below

 WHERE (Child  IS NOT NULL AND parent = 4)
 OR COALESCE(child, 4) = 4

Upvotes: 1

Related Questions