By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,463 Members | 774 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,463 IT Pros & Developers. It's quick & easy.

How to get records against root level id using MySql Query

100+
P: 245
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...?
Dec 13 '11 #1
Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,365
Join the product table to the category table so you can filter on the parent id.
Dec 13 '11 #2

100+
P: 245
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
Dec 13 '11 #3

Rabbit
Expert Mod 10K+
P: 12,365
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.
Dec 13 '11 #4

100+
P: 245
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.
Dec 14 '11 #5

Rabbit
Expert Mod 10K+
P: 12,365
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.
Dec 14 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.