SQL return rows where count of children does not equal column value

Question

I have a table called Item with columns ItemID (PK), ItemName, ExpectedSubItems and another table called SubItem with columns SubItemID (PK), ItemID (FK), SubItemName.

I want to return all rows from Item where the number of SubItems is different from ExpectedSubItems.

I tried to use something like:-

Select * From Item
Join SubItem on Item.ItemID = SubItem.ItemID
Where ExpectedSubItems = Count(SubItem.ItemID)

but that gives me the error:-

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Any ideas from the SQL guru's out there?

Answer 1

you need a sub-query

select *
  from item
  where expectedsubtems <> (
    select count(*)
      from subitem
      where subitem.itemid = item.itemid
    )

Answer 2

try:

Select i.ItemId, i.ItemName
From Item i
  Left Join SubItem s
     On s.ItemID  = i.ItemId
Group By i.ItemId, i.ItemName, i.ExpectedSubItems
Having Count(*) <> i.ExpectedSubitems

Answer 3

This should do it:

SELECT
     I.item_id,
     I.item_name,
     I.expected_subitems
FROM
     Items I
LEFT OUTER JOIN Sub_Items SI ON
     SI.item_id = I.item_id
GROUP BY
     I.item_id,
     I.item_name,
     I.expected_subitems
HAVING
     COUNT(SI.item_id) <> I.expected_subitems

Answer 4

SELECT  ItemID
FROM    Item
JOIN    SubItem
ON      SubItem.ItemID = Item.ItemID
GROUP BY
        ItemID, ExpectedSubItems 
HAVING  ExpectedSubItems <> COUNT(*)

or this (so that you don't have to group by all Item fields and which also works for 0 expected subitems)

SELECT  Item.*
FROM    Item
CROSS APPLY
        (
        SELECT  NULL
        FROM    SubItem
        WHERE   SubItem.ItemID = Item.ItemID
        HAVING  ExpectedSubItems <> COUNT(*)
        ) S

Answer 5

Try the following:

select *
  from Item I
  LEFT OUTER JOIN (select ItemID, COUNT(*) as ActualSubItemCount
                     from SubItem
                     group by ItemID) S
    ON (I.ItemID = S.ItemID)
  where (S.ItemID IS NULL AND NVL(I.ExpectedSubItems, 0) <> 0) OR
        I.ExpectedSubItems <> S.ActualSubItemCount;