SQL query to retrieve all products in some categories in the child list model
I have a database with an Adjacency List approach to handle product categorization where one product can be found in many categories. Take a look at the following database layout:
cats
id parent title desc
1 0 top top level
2 1 Electronics
3 2 Gaming
4 2 Computers
5 4 Tablets
6 1 Food
7 3 Xbox
products
id title qty
1 ToshibaTV 5
2 I-PAD2 9
3 Laser Pen 24
4 Asus Notebook 5
cats_products
id product_id cat_id
1 2 3
2 2 5
3 1 2
4 3 2
5 4 4
In the example above, I need a SQL query that can get all the products found in the Electronics category and any child category with any level (Xbox, for example, which is not directly a child of Electronics) without repeating a product that is found in more than one categories such as I-PAD2.
I could do this with a PHP application, but I am wondering if this can be done using only raw sql in MySQL?
source to share
What you are trying to do is quite tricky with the Adjacency list model. As @Mike suggested, using the nested set model would make this much easier. Or alternatively it will be easier to do it through your php code.
However, assuming you know how many parent-child levels there can be (or you can assume there will be at most X), you can try something like this. This would be easier to read if MySQL had CTE support, but unfortunately it doesn't. In this example, I've gone deep into 4 levels - you can get an idea of going deeper.
SELECT p.Id, p.Title, p.Qty
FROM Products p
JOIN Cats_Products cp on p.id = cp.product_id
WHERE cp.cat_id IN (
SELECT c.id
FROM Cats c
WHERE c.title = 'Electronics'
UNION ALL
SELECT c2.id
FROM Cats c
LEFT JOIN Cats c2 ON c.id = c2.parent
WHERE c.title = 'Electronics'
UNION ALL
SELECT c3.id
FROM Cats c
LEFT JOIN Cats c2 ON c.id = c2.parent
LEFT JOIN Cats c3 ON c2.id = c3.parent
WHERE c.title = 'Electronics'
UNION ALL
SELECT c4.id
FROM Cats c
LEFT JOIN Cats c2 ON c.id = c2.parent
LEFT JOIN Cats c3 ON c2.id = c3.parent
LEFT JOIN Cats c4 ON c3.id = c4.parent
WHERE c.title = 'Electronics'
)
GROUP BY p.Id, p.Title, p.Qty
And here is the SQL Fiddle .
Good luck.
source to share
This is the first part of the question. Could you explain the children's part in more detail. I think it has something to do with cats' parents.
Try the following:
SELECT distinct(p.title)
FROM cats AS c
JOIN cats_products AS cp
ON cp.cat_id = c.id AND c.title = "Electronics"
JOIN products AS p
ON p.id = cp.product_id;
SAMPLES DATA:
CREATE TABLE cats
(`id` int, `parent` int, `title` varchar(11), `desc` varchar(9))
;
INSERT INTO cats
(`id`, `parent`, `title`, `desc`)
VALUES
(1, 0, 'top', 'top level'),
(2, 1, 'Electronics', ''),
(3, 2, 'Gaming', ''),
(4, 2, 'Computers', ''),
(5, 4, 'Tablets', ''),
(6, 1, 'Food', ''),
(7, 3, 'Xbox', '')
;
CREATE TABLE products
(`id` int, `title` varchar(13), `qty` int)
;
INSERT INTO products
(`id`, `title`, `qty`)
VALUES
(1, 'ToshibaTV', 5),
(2, 'I-PAD2', 9),
(3, 'Laser Pen', 24),
(4, 'Asus Notebook', 5)
;
CREATE TABLE cats_products
(`id` int, `product_id` int, `cat_id` int)
;
INSERT INTO cats_products
(`id`, `product_id`, `cat_id`)
VALUES
(1, 2, 3),
(2, 2, 5),
(3, 1, 2),
(4, 3, 2),
(5, 4, 4)
;
EDIT Second part :
SELECT distinct(v.title) FROM products AS v
JOIN (
SELECT Y.product_id FROM cats_products AS Y
JOIN(
SELECT distinct(z.id) FROM cats AS Z
JOIN
(SELECT c.parent
FROM cats AS c
JOIN cats_products AS cp
ON cp.cat_id = c.id
AND c.title = "Electronics") AS X
ON x.parent = z.parent) AS W
ON W.id = Y.cat_id) AS u
ON u.product_id = v.id
;
Sample data
CREATE TABLE cats
(`id` int, `parent` int, `title` varchar(11), `desc` varchar(9))
;
INSERT INTO cats
(`id`, `parent`, `title`, `desc`)
VALUES
(1, 0, 'top', 'top level'),
(2, 1, 'Electronics', ''),
(3, 2, 'Gaming', ''),
(4, 2, 'Computers', ''),
(5, 4, 'Tablets', ''),
(6, 1, 'Food', ''),
(7, 3, 'Xbox', '')
;
CREATE TABLE products
(`id` int, `title` varchar(13), `qty` int)
;
INSERT INTO products
(`id`, `title`, `qty`)
VALUES
(1, 'ToshibaTV', 5),
(2, 'I-PAD2', 9),
(3, 'Laser Pen', 24),
(4, 'Asus Notebook', 5),
(5, 'French Fries', 50)
;
CREATE TABLE cats_products
(`id` int, `product_id` int, `cat_id` int)
;
INSERT INTO cats_products
(`id`, `product_id`, `cat_id`)
VALUES
(1, 2, 3),
(2, 2, 5),
(3, 1, 2),
(4, 3, 2),
(5, 4, 4),
(5, 5, 6)
;
This is my interpretation of your question: each category has a parent and you want to select all other categories with the same parent. More specific all products for the same parent category. SQL FIDDLE DEMO
source to share