MySQL Select query with join

I have a 2 tables that i need information from:

1. Categories (hierarchy (nested set model)): id, name, lft, rgt
2. Items: id, categoryID, name

I need a query that receives all the items from the selected category (incl sub-categories)

This has to be a join query.

Thanks in advance!
entronetAsked:
Who is Participating?
 
Computer101Connect With a Mentor Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0
 
Raynard7Commented:
Hi,

to navigate through hierarchial data tables in mysql there is no syntax to allow you to do so through joins, so you would either have to hard code how many levels you wish to search through, or alternatively build a stored procedure to do it for you.
0
 
entronetAuthor Commented:
Hi, thanks for you answer. So I couldn't use this query to get the category incl. sub-categories and add some sort of subquery or join to receive all the items from the category:

SELECT node.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.name = 'Category_Name'
ORDER BY node.lft;
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Raynard7Commented:
hi - that will just get the children - ie at 1 level - if you have more decendants ie sub-sub categories then this will not pick them up.

If all you want is everything that has the parent of category_name then that is fine and your query will not present problems - I interpreted that you wanted all descendants of the category
0
 
entronetAuthor Commented:
Oh, my mistake, no I wanted to pick up all items from the sub-categories in the given category
0
 
Ioannis AnifantakisSoftware EngineerCommented:

select c.name, c.lft, c.rtg, i.name
from Categories c right join Items i on c.id=i.categoryid
0
 
entronetAuthor Commented:
Sorry, that won't work since I only need the chosen category and it's sub categories/items.
0
 
aolineCommented:
I am keen to see an answer about the recursive problem as well.
Hopefully a guru can suggest something.
Matthew
0
 
entronetAuthor Commented:
This is what i used and it's working fine:

SELECT node.name
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND parent.name = 'Category_Name'
ORDER BY node.lft;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.