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: enter image description here

+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







All Articles