Join two tables where all child records of first table match all child records of second table.

Question

I have four tables: Customer, CustomerCategory, Limit, and LimitCategory. A customer can be in multiple categories and a limit can also have multiple categories. I need to write a query that will return the customer name and limit amount where ALL the customers categories match ALL the limit categories.

I'm guessing it would be similar to the answer here, but I can't seem to get it right. Thanks!

Edit - Here's what the tables look like:

tblCustomer
  customerId
  name

tblCustomerCategory
  customerId
  categoryId

tblLimit
  limitId
  limit

tblLimitCategory
  limitId
  categoryId

Answer 1

I don't know if this will work or not, just a thought i had and i can't test it, I'm sures theres a nicer way! don't be too harsh :)

  SELECT 
   c.customerId
 , l.limitId
FROM 
 tblCustomer c
CROSS JOIN 
 tblLimit l
WHERE NOT EXISTS
(
 SELECT 
  lc.limitId 
 FROM 
  tblLimitCategory lc 
 WHERE 
  lc.limitId = l.id
 EXCEPT
 SELECT
  cc.categoryId 
 FROM 
  tblCustomerCategory cc 
 WHERE 
  cc.customerId = l.id
)

Answer 2

I THINK you're looking for:

SELECT * 
FROM CustomerCategory 
LEFT OUTER JOIN Customer
    ON CustomerCategory.CustomerId = Customer.Id
INNER JOIN LimitCategory
    ON CustomerCategory.CategoryId = LimitCategory.CategoryId
LEFT OUTER JOIN Limit
    ON Limit.Id = LimitCategory.LimitId

Answer 3

This is what I ended up using. By matching on each individual category AND the counts of categories I am guaranteed that all categories match.

TESTED:

select distinct c.name, l.limit
from tblCustomer c
inner join tblCustomerCategory cc on c.customerId = cc.customerId
inner join (select customerId, count(distinct categoryId) as categories
            from tblCustomerCategory
            group by customerId) as customerCount
on c.customerId = customerCount.customerId

inner join tblLimit l
    inner join tblLimitCategory lc on l.limitId = lc.limitId
    inner join (select limitId, count(distinct categoryId) as categories
            from tblLimitCategory
            group by limitId) as limitCount
    on l.limitId = limitCount.limitId
on cc.categoryId = lc.categoryId
and customerCount.categories = limitCount.categories

I'm also testing the following solution which makes use of the new Over() feature to see if it's faster.

UNTESTED:

select distinct c.name, l.limit
from
    tblCustomer c,
    tblLimit l
where exists(
    select cc.categoryId, count(cc.categoryId) over(partition by cc.customerId)
    from tblCustomerCategory cc
    where cc.customerId = c.customerId

    intersect

    select l.categoryId, count(l.categoryId) over(partition by l.limitId)
    from tblLimitCategory lc
    where lc.limitId = l.limitId
)