Count() where the item counted is a JOIN back to another table

Question

I'm pretty sure I could have done a better job with the title of this post. I wasn't sure how to shrink this question down into a working title...

I have two tables. BillItems and SubItems.

SubItems:

SubItemId    ItemId    MasterItemId
-----------------------------------
    1          50          10
    2          50          11
    3          60          10
    4          60          12
    5          70          10

BillItems:

BillItemId    ItemId
---------------------
    1           10
    2           11
    3           50
    4           60
    5           70

Ok, so now, I need to know if BillItems contains any items that are chilren to more than one MasterItem, where the MasterItem is also inside of the BillItems table. I know that sounds confusing, so I'll give an example:

Item 50 is a child item to both Item 10 and Item 11. Item 10 and Item 11 are both in the BillItems table. So, I need Item 50 to show up in the query.

Item 60 is a child to both Item 10 and Item 12. Item 10 is in the BillItems table, but Item 12 is not. So I don't want Item 60 to show up in the query.

Items 10 and 11 are not children in SubItems. So I don't want it to show up in the query.

EDIT:

The desired output, with the above data, would be simply:

ItemId
------
  50

Answer 1

I believe this is what you're looking for:

SELECT si.ItemId
FROM SubItems si
WHERE EXISTS (SELECT 1 -- This EXISTS may be omitted if SubItems.ItemId has an enforced FOREIGN KEY reference to BillItems.ItemId
              FROM BillItems bi
              WHERE bi.ItemId = si.ItemId) 
  AND EXISTS (SELECT 1
              FROM BillItems bi
              WHERE bi.ItemId = si.MasterItemId)
GROUP BY si.ItemId
HAVING COUNT(DISTINCT si.MasterItemId) > 1;

Answer 2

You can use a correlated sub-query to check if a masteritemid is present in the billitems table. For id's that aren't present you get a count of 0. You can then group by the itemid and eliminate all such items where there is a missing item and when there are more than 1 items in the itemid group.

select itemid 
from (select itemid,(select count(*) from billitems where s.masteritemid=itemid) as present_or_not
      from subitems s
      ) x 
group by itemid 
having count(case when present_or_not=0 then 1 end)=0 and count(*) > 1