How to find grandchildren?
I am trying to find all grandchildren for a specific id, but I cannot figure out if my relationship is correct. Below is the code, but I need to get the associated products from the product table.
SELECT b.category_id, b.category_name, b.parent, a.parent
FROM categories AS a, categories AS b
WHERE a.category_id = b.parent AND a.parent = 119
When I try to connect to the product table, I get an error:
SELECT *
FROM products p
INNER JOIN products_categories pc
ON p.product_id = pc.product_id
INNER JOIN (
SELECT b.category_id, b.category_name, b.parent, a.parent
FROM categories AS a, categories AS b
WHERE a.category_id = b.parent AND a.parent = 119
)x
My desired output would be as follows: (NOTE: Check out my SQL script to see the schema as code)
(76, 'BR134', 'LEA530664', 'ITEM1234', 1499.99, 'yes', 'This is a nice gun'),
(77, 'mZCXGT', 'LEA471061', 'qwer345', 89.99, 'yes', 'Testing!'),
(78, 'ert', 'LEA023991', 'asdf34', 129.99, 'yes', 'Another test to get this right!'),
(79, 'model test', 'LEA355935', 'item test', 119.99, 'yes', 'This is another test dammit!'),
(80, 'CZV1354', 'LEA741837', 'LI-1234', 1299.99, 'yes', 'This is a hipoint weapon!'),
(81, 'PINK12G', 'LEA008558', 'PINK-SHG', 89.99, 'yes', 'YEP! This is pink!'),
(82, 'BOWTECH', 'LEA762521', 'asdf', 899.99, 'yes', 'This is a test!'),
(83, 'LX32', 'LEA346903', 'MADEUP', 1499.99, 'yes', 'This is Carters gun.');
SQL Fiddle:
http://sqlfiddle.com/#!2/dd66c/2
Here's my schema:
+3
source to share
1 answer
I think what you are looking for is this (I added the category names for "debugging"):
SELECT
p.product_id
, p.model
, p.sku
, p.item_number
, p.msrp
, p.availability
, p.description
, grand_child.category_name AS grand_child_category
, child.category_name AS child_category
, parent.category_name AS parent_category
FROM categories parent
INNER JOIN categories child
ON parent.category_id = child.parent
INNER JOIN categories grand_child
ON child.category_id = grand_child.parent
INNER JOIN products_categories pc
ON grand_child.category_id = pc.category_id
INNER JOIN products p
ON p.product_id = pc.product_id
WHERE parent.category_id = 119
+2
source to share