SELECT records based on value of one of those records

[ppetree]

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!

[smantscheff]

select * from test 
where name = 'Meals' 
or parent = (select id from test where name = 'Meals');

[Morta]

Hiii, I agree with Smantscheff... check this code

SELECT * FROM test WHERE name = 'Meals' OR parent = (SELECT id FROM test WHERE name = 'Meals') order by id asc;

%^^%Murtada%^^%

[ppetree]

select * from test 
where name = 'Meals' 
or parent = (select id from test where name = 'Meals');

Absoultely freaking genius!

I just couldnt wrap my head around this until I saw your code and then it made perfect sense!

Thanks guys!