items table
id category_id item_title
1 2 Seagate SSHD
2 3 Seagtate 2.5 inch HDD
3 4 Western Digital 3.5 inch HDD
4 4 Toshiba 3.5 inch HDD
5 5 Generic Used Monitor
6 6 ASUS Monitor
categories table
id parent_id category_title
1 0 Hardware
2 1 Hard Disks
3 2 Laptop Hard Disk
4 2 Desktop Hard Disk
5 1 Monitor
6 5 LCD Monitor
Visual:
-- Laptop Hard Disk
-- Hard Disks |
Harware-| -- Desktop Hard Disk
-- Monitors -- LCD Monitor
Query:
SELECT * FROM items_table p
JOIN categories_table cp ON p.category_id = cp.id
JOIN categories_table cc ON cp.parent_id = cc.id
WHERE cc.id = "1"
Output
id category_id item_title
1 2 Seagate SSHD
5 5 Generic Used Monitor
But, I am trying to get items from all sub-category too, i.e; 1.when parent category is requested must show everything from parent, child and grand-child category 2.when child category is requested must show everything from child and its child(grand-child)
Expectation; when requested for category 1 (hardware)
id category_id item_title
1 2 Seagate SSHD
2 3 Seagtate 2.5 inch HDD
3 4 Western Digital 3.5 inch HDD
4 4 Toshiba 3.5 inch HDD
5 5 Generic Used Monitor
6 6 ASUS Monitor
when requested for category 2 (hard disks)
id category_id item_title
1 2 Seagate SSHD
2 3 Seagtate 2.5 inch HDD
3 4 Western Digital 3.5 inch HDD
4 4 Toshiba 3.5 inch HDD
How can I modify my query to get all items from category and all of it's sub-sub-category ?
I have to warn that id-parentId method (known as Adjacency List) isn't good for an arbitrary level of nesting. But if you know that it will be 3-tier only, let it be. Otherwise look at alternatives like Nested Sets or Materialized Paths.
The solution is to get all the required category IDs from all 3 levels of categories and then to apply them to the items table.
SELECT *
FROM items
WHERE category_id IN (
SELECT :cat_id AS id
UNION ALL
SELECT c2.id FROM categories AS c2
WHERE c2.parent_id = :cat_id
UNION ALL
SELECT c3.id FROM categories AS c3
JOIN categories AS c3p ON c3p.id = c3.parent_id
WHERE c3p.parent_id = :cat_id
)