asked
Query to displat only category and not sub-category, also filter few category/sub-category for the products.
![alt text][1] ![alt text][2] [1]: /storage/temp/554-1.png [2]: /storage/temp/555-2.png
It would be better to type the question rather than post an image of the text - then we can copy and paste queries rather than having to type them out again - this will help you to get better and quicker answers.
to format text in a question use the buttons above the text editor. Code can be preceded 4 spaces to indent and format it, making a line a quote is done by starting the line with > and a space.
Initially placed it as text only but the table designs were showing up as continuous text and was not readable, so I placed it as an image here. Here is the query:
select p.productname,
(case when c.Parent-Category-Id is null
then c.category
when c.Parent-Category-Id is not null
then (select category from category where id=c.Parent-Category-Id)end
)as category
from product p inner join category c on p.category=c.id
where c.category not in ('LPG')
One way (not necessarily the fastest) is to use a recursive CTE to keep track of the root category for any one sub-category: WITH rcte_categories AS ( SELECT id ,category ,category AS root_category FROM categories WHERE parent_category_id IS NULL UNION ALL SELECT
c.id ,c.category ,r.root_category FROM rcte_categories r INNER JOIN categories c ON c.parent_category_id =
r.id ) SELECT p.product ,c.root_category FROM products p INNER JOIN rcte_categories c ON p.category =
c.id WHERE c.category NOT IN ('LPG') The rCTE provides both the current category (for filtering) and the root category (for display). Of course, this only allows filtering products which have that *exact* category; it does not remove products belonging to *children* of the filtered categories -- these will still be displayed. To remove them too, you would have to put the filtering logic in the rCTE instead, before and after the `UNION ALL`.
12 People are following this question.
