SQL Find Sets with Common Members (Relational Division)

SQL find sets with common members (relational division)

I think this should also work

select distinct g.GroupID, c.ClassID
from @Groups g
    left join @Classes c on g.TagID = c.TagID
where not exists (
    select *
    from @Groups g2
    where g2.GroupID = g.GroupID
        and g2.TagID not in (
            select TagID
            from @Classes c2
            where c2.ClassID = c.ClassID
        )
    ) or c.ClassID is null

SQL: select sets containing exactly given members

from your phrase

I want to select groupids which has members m1,m2 but no other members

try this one, the idea behind is to count the total instances of records that match the condition and the where clause and that it is equal to the total number of records per group.

SELECT groupid
FROM table1 a
WHERE memberid IN ('m1','m2')
GROUP BY groupid
HAVING COUNT(*) = 
(
  SELECT COUNT(*)
  FROM table1 b
  WHERE b.groupid = a.groupid
  GROUP BY b.groupID
)

SQLFiddle Demo

Relational division: find each unique set of child values in a column

I see, you want the "unique" combinations of children, regardless of order.

The following gets parents that are equivalent:

select m1.Parent as Parent1, m2.Parent as Parent2
from (select m.*, count(*) over (partition by Parent) as NumKids
      from #m m
     ) m1 join
     (select m.*, count(*) over (partition by Parent) as NumKids
      from #m m
     ) m2
     on m1.ChildID = m2.ChildID
group by m1.Parent, m2.Parent
having count(*) = max(m1.NumKids) and max(m1.NumKids) = max(m2.NumKids);

We can now get what you want using this

with parents as (
    select m1.Parent as Parent1, m2.Parent as Parent2
    from (select m.*, count(*) over (partition by Parent) as NumKids
          from #m m
         ) m1 join
         (select m.*, count(*) over (partition by Parent) as NumKids
          from #m m
         ) m2
         on m1.ChildID = m2.ChildID
    group by m1.Parent, m2.Parent
    having count(*) = max(m1.NumKids) and max(m1.NumKids) = max(m2.NumKids)
)
select distinct m.*
from (select min(Parent2) as theParent
      from parents
      group by Parent1
     ) p join
     #m m
     on p.theParent = m.Parent;

If you want a new id instead of the old one, use:

select dense_rank() over (partition by m.Parent) as NewId, m.ChildID

in the select.

Find records with a single common field, but different values for other fields

Try this

SELECT DISTINCT p.*
FROM Promotions p 
    JOIN Promotions q ON p.promoid = q.promoid AND p.id <> q.id
WHERE (p.customerId <> q.customerId) 
    OR (p.dealPeriod <> q.dealPeriod) 
    OR (p.lob <> q.lob)

Relational Division - Mimicking 'ONLY IN'

You could use set operators:

SELECT ID FROM tab WHERE FileType IN ('jpg', 'png')
MINUS
SELECT ID FROM tab WHERE FileType NOT IN ('jpg', 'png')

Assumption: FileType is not nullable.

---

Handling NULL:

SELECT ID FROM tab WHERE FileType IN ('jpg', 'png')
MINUS
SELECT ID 
FROM (SELECT * FROM tab WHERE FileType IS NOT NULL) 
WHERE FileType NOT IN ('jpg', 'png')

---

Related Topics