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

Question

I'm sure I could better handle the title of this post. I was not sure how to change this question to a working title ...

I have two tables. BillIntemes and subtitles.

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

So now, I need to know if BillItems contain any elements that are chilren for multiple MasterItem, where MasterItem is also inside the BillItems table. I know this sounds confusing, so I will give an example:

Item 50 is a child of both item 10 and item 11. Item 10 and item 11 are in the BillItems table. So, I need element 50, which will be displayed in the request.

60 10, 12. 10 BillItems, 12 - . , Item 60 .

10 11 SubItems. , .

EDIT:

:

ItemId
------
  50

Answer 1

, , :

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

- :

var result=from b in context.BillItems
           let masters=context.SubItems.Where(s=>s.ItemId==b.ItemId).Select(s=>s.MasterItemId)
           where masters.All(e=>context.BillItems.Any(x=>x.ItemId==e))
           select b.ItemId;

, sql:), @Casey , Linq. ( EF nav) :

var result=context.BillItems.Where(b=>b.SubItems.All(s=>context.BillItems.Any(x=>x.ItemId==s.MasterItemId))
                            .Select(e=>e.ItemId);

:

var result=from b in context.BillItems
           join s in context.SubItems on b.ItemId equals s.ItemId into g
           where g.All(e=>context.BillItems.Any(x=>x.ItemId==e.MasterItemId))
           select b.ItemId;

Answer 3

, , masteritemid billitems. id, , 0. group by itemid , itemid 1 .

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

Answer 4

, .

,

select a.ItemId ItemId_a
    --, count(distinct a.MasterItemId) , count(distinct b.ItemId)
    from SubItems a left join BillItems b
    on a.MasterItemId = b.ItemId
    group by a.ItemId
    having count(distinct a.MasterItemId)  = count(distinct b.ItemId)
    and count(distinct a.MasterItemId)>1
    ;

Answer 5

:

/*
create table BillItems (BillItemId int, ItemId int)
create table Subitems (SubitemId int, ItemId int, MasterItemId int)

insert BillItems values (1,10)
insert BillItems values (2,11)
insert BillItems values (3,50)
insert BillItems values (4,60)
insert BillItems values (5,70)

insert Subitems values(1,50,10)
insert Subitems values(2,50,11)
insert Subitems values(3,60,10)
insert Subitems values(4,60,12)
insert Subitems values(5,70,10)
*/

;with x as (select itemId from subitems group by itemId having count(*) > 1)
, y as (select s.ItemId, b.BillItemId from x join subitems s on x.itemid = s.itemid left join Billitems b on s.MasterItemId = b.ItemID)
select distinct itemid from y
except 
select itemid from y where billitemid is null

Answer 6

- :

SELECT * FROM
    (SELECT DISTINCT ItemId FROM TABLE1 t WHERE t.MasterId IN (SELECT ItemId FROM TABLE2)) as MasterInTable2
    EXCEPT
    (SELECT DISTINCT ItemId FROM TABLE1 t WHERE t.MasterId NOT IN (SELECT ItemId FROM TABLE2)) as
    MasterNotInTable2

Answer 7

CREATE TABLE #SubItems (
                            id INT IDENTITY(1,1),
                            subItemID   INT,
                            ItemID      INT,
                            MasterItemID INT
                        )

INSERT INTO #SubItems
VALUES(1,50,10) 
INSERT INTO #SubItems
VALUES(2,50,11) 
INSERT INTO #SubItems
VALUES(3,60,10) 
INSERT INTO #SubItems
VALUES(4,60,12) 
INSERT INTO #SubItems
VALUES(5,70,10) 

CREATE TABLE #BillItems (
                            id INT IDENTITY(1,1),
                            BillItemID  INT,
                            ItemID      INT
                        )

INSERT INTO #BillITems
VALUES(1,10)    
INSERT INTO #BillITems
VALUES(2,11)    
INSERT INTO #BillITems
VALUES(3,50)    
INSERT INTO #BillITems
VALUES(4,60)    
INSERT INTO #BillITems
VALUES(5,70)    


SELECT A.ItemID
FROM (
        SELECT bi.ItemID, COUNT(*) AS CountBill
        FROM #BillItems bi
        JOIN #SubItems si ON
            bi.ItemID = si.ItemID
        GROUP BY bi.ItemID
     ) A 
        JOIN #SubItems si ON
            A.ItemID = si.ItemID
WHERE si.MasterItemID IN (SELECT ItemID FROM #BillItems)
GROUP BY A.ItemID
HAVING COUNT(*) > 1



DROP TABLE #SubItems
DROP TABLE #BillItems