CODEWITHSUNDEEP
sqlparentjoin
Vehlad
Vehlad

Reputation: 155

SQL Query to select products from all categories of One Parent

I have a Product Table with this structure

 id, title, slug, details, category(FK category.ID)

And a Category Table: id, name, slug, parent

Categories have only 1 level If there is no parent then parent field is 0 else id of parent category saved

Suppose I have categories structure like this:

FRUITS
|---- APPLE
|---- MANGO
|---- BANANA

I use a Category slug to query products from a category

category.php?cat=apple category.php?cat=mango

1st query:

select id,name from category WHERE slug='$catslug'

After getting ID of category then use query to get results of that ID from products table

select * from products where category=$categoryid

What query is required if I want to fetch all products posted in FRUITS?

category.php?cat=fruits

I want to get all products posted in apple, mango, banana (all children of fruits)

Only the child IDs are saved in product table how do I link parent category apple with these?

Upvotes: 1

Views: 63364

Answers (3)

Vehlad
Vehlad

Reputation: 155

I used 2nd query suggested by tarmaq

SELECT * 
    FROM products
    WHERE category IN (
    SELECT id
    FROM category
    WHERE slug = '$cats'
    UNION
    SELECT c.id
    FROM category c
    JOIN category c2 ON c2.id = c.parent
    WHERE c2.slug = '$cats')";

Its showing results from products table only Not getting Category Name, Category Details from category table

I tried this:

SELECT * 
    FROM products
    WHERE category IN (
    SELECT *
    FROM category
    WHERE slug = '$cats'
    UNION
    SELECT *
    FROM category c
    JOIN category c2 ON c2.id = c.parent
    WHERE c2.slug = '$cats')";

result: Error in Query.

also I want to JOIN users table in above query and to show author name(users.name) alongwith product (author id saved in product table which is FK from users table users.id is unique)

JOIN users on products.author = users.id

Where to put this Join statement in above query

EDIT - I joined user table with author name with this query

SELECT * 
FROM products
JOIN users U ON U.id = products.author
WHERE category IN (
SELECT id
FROM category
WHERE slug = '$cats'
UNION
SELECT C.id
FROM category C
JOIN category C2 ON C2.id = C.parent
WHERE C2.slug = '$cats') 
ORDER BY products.date DESC

now want to fetch data from category table catagory.name, category.details with this query

Upvotes: 0

tarmaq
tarmaq

Reputation: 422

1) You don't need to use two queries, you can use join:

SELECT p.id, p.title, p.slug ...
FROM products p
JOIN categories c ON c.id = p.category
WHERE c.slug = 'apple'

2) You can select all fruit products with this query:

SELECT id, title, slug ...
FROM products
WHERE category IN (
  SELECT id
  FROM categories
  WHERE slug = 'fruits'
  UNION
  SELECT c.id
  FROM categories c
  JOIN categories c2 ON c2.id = c.parent
  WHERE c2.slug = 'fruits'
)

Upvotes: 2

mellamokb
mellamokb

Reputation: 56769

You can look for categories linked to products where the parent category is the fruit category by using multiple joins. Here cc refers to the child category and cp refers to the parent category. We retrieve all products having a child category equal to the products category, and having a parent category to that child category equal to FRUITS:

    select *
      from products p
inner join categories cc on cc.category = p.category
inner join categories cp on cp.category = cc.parentcategory
     where cp.category = 'FRUITS'

If this needs to be a generic solution that handles both parent and child categories seemlessly, you can check for a match at either level (assuming all category names are unique, and every product has a category):

    select *
      from products p
inner join categories cc on cc.category = p.category
 left join categories cp on cp.category = cc.parentcategory
     where cc.category = '$category' or cp.category = '$category'

Upvotes: 3

Related Questions