OK, I have a table that has:
If parent contains NULL it IS the "parent" otherwise it is the child of a parent
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:
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...
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!
Edited by ppetree: more info