search by category and master category

[eluser]egoslip[/eluser]
I can search by cat id but I don't know how to search by master id like this for example.

id|mid|name
1 |0 |Master Category
2 |1 |Sub category

id|cat_id|name
1 |2 |Results I want.

Now i can search by the cat id and if i select sub category i can see the result but if I select the master category how can I select the result...

So my question is how can i select the result by master category with out adding mcat_id to the results table.

I believe it has something to do with join but I have no clue on how to join. if you can help me out or point me in the right direction that would be great thanks

[eluser]rogierb[/eluser]
<code>
select ta.id, t.id, t.name from table t
left join table ta on (t.mid = ta.id)
where t.id = 'category_id'
</code>

Something like this?

[eluser]egoslip[/eluser]
I really have no clue what that all means i no clue how to use join and what is "ta."id

it just looks like a bunch of initials I dont understand what does what

Thanks for the reply but if you could break it down that would be very helpful.

[eluser]rogierb[/eluser]
Dude, you really need to learn MySQL, since this is very basic stuff:-)

<code>
select ta.id, t.id, t.name from master_table t
left join master_table ta on (t.mid = ta.id)
where t.id = ‘category_id’
</code>

"t" and "ta" are dummy names (aliases) that represent the corresponding table. if it says " tablename abc" then "abc.id" is the same as "tablename.id". Which is ofcourse the id from table "tablename"

What I've done if join the table with itself, so if your table is called "my_table" then both "t" and "ta" represent that table. joining is nothing more then saying that if 2 tables have something common, use that thing they have in common as a selector for the table that is joined.(more or less)

Just read up upon joins on mysql.com and you'll get it fast enough

[eluser]egoslip[/eluser]
I think i get it but where are you selecting the second table??

[eluser]egoslip[/eluser]
Ahh I think i got it

select cat.id, cat.mid, prod.* from products prod
left join categories cat on (prod.cat_id = cat.id)
WHERE cat.id='1' OR cat.mid='1'

There seems to be some magic voodoo that makes this work but hey I believe i learned something new today thanks for help