How to get records against root level id using MySql Query

[neovantage]

Hi,
I have a scenario in which i am stuck with in my custom shopping cart.

I have tables of category and product.Each product is against some category. for example


Category Table
Fields are catid, parentid, catname

Data
catid = 1, parentid = 0, catname = Electroics
catid = 2, parentid = 1, catname = Computers
catid = 3, parentid = 2, catname = Sony
catid = 4, parentid = 2, catname = HP

Product Table
Fields are prodid, catid, prodname

Data
prodid = 1, catid = 3, prodname = VAIO Laptops
prodid = 2, catid = 3, prodname = VAIO Desktop
prodid = 3, catid = 3, prodname = VAIO Signature Collection
prodid = 4, catid = 4, prodname = HP Laptops
prodid = 5, catid = 4, prodname = HP Desktop

Now what i want that when some one select category computers then it will get all the records which are either Sony Products or HP Products. I want to write MySQL query but unable to write. Can come one guide me how can i write query for this...?

[Rabbit]

Join the product table to the category table so you can filter on the parent id.

[neovantage]

Yes but it gives me 1 to 1 result.
I mean look at my format of query
select * from category c, product p where c.catid=p.catid and p.catid={posted-catid}

Now if i select sony then it will give me the products which relates to sony and if i select hp then it will give me the products which relates to hp but when i will click on Electronics, It does not return any record as Electronic id is not there in the product table & here is my point where i stuck. What i want is that when i click on Electronics then it shows me all the products which comes under Electronic child categories. So please help me out to sort out my problem

[Rabbit]

Because of the data structure you've chosen, you need as many joins as you have levels of categories. If you need a dynamic hierarchy, I suggest you switch over to a preorder tree structure. That will allow you to have as many levels of hierarchy as you want without increasing the amount of joins needed.

Either way, once you have all the joins needed for the full path down to the child node, you can filter on any chosen level. With your current data structure where you have a three-level hierarchy, you need to join the category table three times to get the data needed for each level. Then you can filter on any of those levels.

[neovantage]

Can you guide me the query format as i don't have that level expertise to write query. That will be really helpful for me.

[Rabbit]

Give it a shot and post your resultant SQL and we will help you through the specifics. The key part is to join the category table 3 times to get your 3-level hierarchy.