CODEWITHSUNDEEP
sqlsql-server-2005
Christopher Edwards
Christopher Edwards

Reputation: 6659

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

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?

Upvotes: 2

Views: 5329

Answers (5)

Tom H
Tom H

Reputation: 47454

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

Upvotes: 1

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

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;

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425361

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

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146499

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

Upvotes: 1

Ray
Ray

Reputation: 21905

you need a sub-query

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

Upvotes: 3

Related Questions