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

Question

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

where is the information of piece numbers which are not in the table ie 3,4,5 comes from ? Are you assuming the PieceNo is a continuous number that starts from 1 ? — Squirrel
– Squirrel, Commented Oct 23, 2019 at 6:41

Squirrel · Accepted Answer · 2019-10-23 07:25:04Z

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
)

Ed Bangga · Accepted Answer · 2019-10-23 07:35:45Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related