OK, I have a table that has:
id (int),
name (varchar),
parent (int)
If parent contains NULL it IS the "parent" otherwise it is the child of a parent
So:
id=1
name=Meals
parent=0
Means the above record is a "parent" (there are 100's of parents). So far, so good?
Next I have a records that belong to parents:
id=2
name=breakfast
parent=1
id=3
name=lunch
parent=1
id=4
name=dinner
parent=1
id=5
name=cars
parent=0
id=6
name=ford
parent=5
Most all of the above entries refer to parent record #1 "Meals" but id 5 is also a parent and id 6 belongs to parent 5.
Now, if I get passed in "Meals" as a search parameter, I need to return all records that have the id of meals in their parent field plus the parent field itself.
So a query on "Meals" which has a record id of 1 should return all recods that have a parent of 1 PLUS the record of the parent... I WANT to get back:
[id]=1, [name]=Meals, [parent]=0
[id]=2, [name]=breakfast, [parent]=1
[id]=3, [name]=lunch, [parent]=1
[id]=4, [name]=dinner, [parent]=1
I have no idea how to write this query...
I have tried this:
SELECT *
FROM test AS t1
LEFT JOIN test AS t2 ON t1.id = t2.parent
WHERE t1.name = "meals"
And I get
[id]=1, [name]=Meals, [parent]=0, [id]=2, [name]=breakfast, [parent]=1
[id]=1, [name]=Meals, [parent]=0, [id]=2, [name]=lunch, [parent]=1
[id]=1, [name]=Meals, [parent]=0, [id]=2, [name]=dinner, [parent]=1
But I dont need the results prefaced with the original search record so I've no clue how to write this to get what I want back out.
SELECT * FROM test WHERE name='Meals' JOIN ????
I'm clueless and would appreciate some help!
Thanks!