Query to get items which are in list and not in list

user_4580872 0
Viewed 321

I have a table 'tbl_Items' with below columns

    [Id] [int] NULL,
    [ItemNo] [varchar](50) NULL,
    [TotalPieces] [int] NULL

and another table 'tbl_ItemPieces' with below columns

    [Id] [int] NULL,
    [ItemId] [int] NULL,
    [PieceNo] [int] NULL

sample values are like below:

tbl_Items

Id  ItemNo  TotalPieces
1   1001    5
2   1002    3
3   1003    4

tbl_ItemPieces

Id  ItemId  PieceNo
1   1       1
2   1       2
3   2       1
4   2       3
5   3       3
6   3       4

I have used below query to get count of available pieces and total pieces and available pieces numbers as comma separated string. 

    select 
        a.ItemNo, COUNT(b.PieceNo) ActualPieces, a.TotalPieces,    

        STUFF((SELECT ', ' + CAST( PieceNo as varchar(50))
        FROM tbl_ItemPieces b 
        WHERE b.itemId = a.Id
        FOR XML PATH('')), 1, 2, '')    

    from tbl_Items a
    inner join tbl_ItemPieces b
    on a.Id = b.itemId
    group by a.ItemNo, a.TotalPieces, a.Id

which results below 

ItemNo  ActualPieces    TotalPieces AvailablePieces
1001    2                  5            1, 2
1002    2                  3            1, 3
1003    2                  4            3, 4

I want another column as a comma separated string which contains piece numbers which are not in the table, say if 5 is the total and pieces included are 1,3 then this column value is '2,4,5'

expected result 

ItemNo  ActualPieces    TotalPieces  AvailablePieces NotAvailablePieces
1001    2               5            1, 2            3,4,5
1002    2               3            1, 3            2
1003    2               4            3, 4            1,2
sql-server sql
2 Answers

the solution below uses a recursive cte (cte_AllPieceNo) to generate a list of possible PieceNo for each Items

From there, just use that and check for NOT EXISTS() in tbl_ItemPieces

; with
cte_AllPieceNo as                 -- Added this
(
    select  Id, TotalPieces, PieceNo = 1
    from    tbl_Items
    union all
    select  Id, TotalPieces, PieceNo = PieceNo + 1
    from    cte_AllPieceNo
    where   PieceNo < TotalPieces
)
SELECT   
    a.ItemNo, 
    COUNT(b.PieceNo) ActualPieces, 
    a.TotalPieces,
    STUFF(( SELECT  ', ' + CAST( PieceNo as varchar(50) )
            FROM    tbl_ItemPieces b 
            WHERE   b.ItemId = a.Id
            FOR XML PATH('')), 1, 2, '') as AvailablePieces,
    STUFF(( SELECT  ', ' + CAST( c.PieceNo as varchar(50) ) -- added this
            FROM    cte_AllPieceNo c
            WHERE   c.Id    = a.Id
            AND     NOT EXISTS
                    (
                        SELECT  *
                        FROM    tbl_ItemPieces d
                        WHERE   d.ItemId    = c.Id 
                        AND     d.PieceNo   = c.PieceNo
                    )
            FOR XML PATH('')),1,2, '') as NotAvailablePieces
FROM    tbl_Items a
        INNER JOIN tbl_ItemPieces b on a.Id = b.ItemId
GROUP BY a.ItemNo, 
         a.TotalPieces, 
         a.Id

if you have a tally table, you can use that to replace the recursive cte

Here is the section of the code that uses tally table.

cte_AllPieceNo as
(
    select  Id, PieceNo = n
    from    tbl_Items
            cross join tally
    where   n >= 1
    and     n <= TotalPieces
)
user_1856470

only way to achieve this using recursive query.

first, is to generate those PieceNo using cte, then join the result to your original query excluding the PieceNo.

with cte as (
        select a.id, (TotalPieces - count(1)) as ct, 1 as ctr, TotalPieces
        from tbl_Items a
        inner join tbl_ItemPieces b on a.Id = b.itemId
        group by a.id, TotalPieces
        union all 
        select id, ct , ctr + 1, TotalPieces from cte where ctr < TotalPieces
    ) select a.ItemNo, COUNT(b.PieceNo) ActualPieces, a.TotalPieces,
        STUFF((select ', ' + CAST( PieceNo as varchar(50))
            from tbl_ItemPieces b 
            where b.itemId = a.Id
            FOR XML PATH('')), 1, 2, ''),
        STUFF((select ', ' + CAST( ctr as varchar(50))
            from cte b 
            where b.id = a.Id and concat(id, ctr) not in (select concat(itemid, pieceno) from tbl_ItemPieces)
            FOR XML PATH('')), 1, 2, '')
        from tbl_Items a
        inner join tbl_ItemPieces b
        on a.Id = b.itemId
        group by a.ItemNo, a.TotalPieces, a.Id
user_64290720
Related