Show Group Above Details Query

Question

Data in Table :

    ItemCode  ItemName     ItemGroup  
    SFD       Softdrink     NULL
    CCL       Coco Cola     SFD
    FNT       Fanta         SFD
    SPR       Sprite        SFD
    ACL       Alchol            
    TQL       Tequila       ACL
    VDK       Vodka         ACL

When user find "Softdrink" then the result will be:

    ItemCode       ItemName
    SFD            Softdrink     
    CCL            Coca Cola         
    FNT            Fanta                       
    SPR            Sprite

How could I do it?

Thank you.

Answer 1

All softdrinks are in itemgroup sfd. Try select itemcode, itemname from table where itemgroup = 'SFD';

Mark Hamilton points out:

Except Softdrink itself doesn't have SFD as its ItemGroup. You'd need to add "or ItemCode = 'SFD'" to your query. I wonder whether the categories can nest further, in which case a recursive CTE would do the trick

Yeah, either add an 'or' to the where, or go recursive.

Answer 2

Got it Worked with

select ItemCode, ItemName from Items 
where itemCode in (select itemcode  from Items where itemname = 'softDrink') 
or itemGroup in  (select itemcode  from Items where itemname = 'softDrink')

Can this been modified further.

Answer 3

WITH hier (code, name) AS
        (
        SELECT  ItemCode, ItemName
        FROM    Table
        WHERE   ItemCode = 'SFD'
        UNION ALL
        SELECT  ItemCode, ItemName
        FROM    hier
        JOIN    Table
        ON      ItemGroup = code
        )
SELECT  *
FROM    hier

This will select items even with deeper hierarchy level (i. e. you can add Fanta Lemon and Fanta Orange with ItemGroup = FNT, and this query will select them too).