[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1625
  • Last Modified:

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!
0
entronet
Asked:
entronet
1 Solution
 
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
 
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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
 
Computer101Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now