question

rk5578 avatar image
rk5578 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
sql-server-2008-r2homework
1.png (33.6 KiB)
2.png (23.2 KiB)
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
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.
3 Likes 3 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
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.
3 Likes 3 ·
rk5578 avatar image rk5578 commented ·
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')
Thanks
1 Like 1 ·

1 Answer

·
jimbobmcgee avatar image
jimbobmcgee answered
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`.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.