Select parent and child entries with Group of Child entries

Mohammad Farook 161 Reputation points
2021-10-06T14:08:03.107+00:00

Hi,

I need select Child table entries and when group ID matched select all child entries

CREATE TABLE #Master (PID VARCHAR(100),Amount DECIMAL(18,3))
INSERT INTO #Master (PID,Amount) VALUES ('P-10001',25000),('P-10002',52000)

CREATE TABLE #MasterSub (PID VARCHAR(100),CID INT)
INSERT INTO #MasterSub (PID,CID) VALUES ('P-10001',500),('P-10002',450),('P-10002',653)

CREATE TABLE #CMaster (CID INT,Description VARCHAR(100),Type VARCHAR(20),ParentID INT)
INSERT INTO #CMaster (CID,Description,Type,ParentID) VALUES (500,'Brush','ITEM',10)
,(450,'Furniture','ITEM',20),(653,'Painting','GROUP',510)
,(654,'L-Painting','ITEM',653)
,(655,'Acces-Painting','ITEM',653)
,(656,'Cont-Painting','ITEM',653)
,(657,'Wastagee-Painting','ITEM',653)

SELECT * FROM #Master
SELECT * FROM #MasterSub
SELECT * FROM #CMaster
DROP TABLE #Master,#MasterSub,#CMaster

138215-tables.jpg

I want exact result below
138194-result.jpg

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,596 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 114.4K Reputation points
    2021-10-06T19:31:16.373+00:00

    If the hierarchy is simple, then try this query:

    ; 
    with Q as
    (
        select PID, 0 as CID, cast(PID as varchar(max)) as Description, '' as Type
        from #Master
        union all
        select m.PID, ms.CID, cm.Description, cm.Type
        from #Master m
        inner join #MasterSub ms on ms.PID = m.PID
        inner join #CMaster cm on cm.CID = ms.CID
        union all
        select m.PID, cm2.CID, cm2.Description, cm2.Type
        from #Master m
        inner join #MasterSub ms on ms.PID = m.PID
        inner join #CMaster cm on cm.CID = ms.CID
        inner join #CMaster cm2 on cm2.ParentID = ms.CID
        where cm.Type = 'GROUP'
    )
    select Description, case CID when 0 then '' else cast(CID as varchar(max)) end as CID, Type
    from Q
    order by PID, CID
    
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. EchoLiu-MSFT 14,581 Reputation points
    2021-10-07T06:10:44.343+00:00

    Maybe like this:

    SELECT m.PID,c.Description,c.CID,c.Type   
    FROM #Master m  
    JOIN #MasterSub ms ON m.PID=ms.PID  
    JOIN #CMaster c ON c.CID=ms.CID OR c.ParentID=ms.CID  
    

    Output:
    138385-image.png

    If you have any question, please feel free to let me know.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments No comments