联接两个表,其中第一个表的所有子记录与第二个表的所有子记录都匹配

我 思考 你在找:

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

更新!

感谢费利克斯指出了我现有解决方案中的一个缺陷(在我最初发布它3年后,呵呵)。再看一遍,我想这可能是对的。这里我得到(1)匹配类别的客户和限额,加上匹配类别的数量,(2)每个客户的类别数量,(3)每个限额的类别数量,(4)然后我确保客户和限额的类别数量与客户和限额之间的匹配数量相同:

未经考验!

select
  matches.name,
  matches.limit

from (
    select
      c.name,
      c.customerId,
      l.limit,
      l.limitId,
      count(*) over(partition by cc.customerId, lc.limitId) as matchCount
    from tblCustomer c
    join tblCustomerCategory cc on c.customerId = cc.customerId
    join tblLimitCategory lc on cc.categoryId = lc.categoryId
    join tblLimit l on lc.limitId = l.limitId
) as matches

join (
    select
       cc.customerId,
       count(*) as categoryCount
     from tblCustomerCategory cc
     group by cc.customerId
) as customerCategories
on matches.customerId = customerCategories.customerId

join (
    select
      lc.limitId,
      count(*) as categoryCount
    from tblLimitCategory lc
    group by lc.limitId
) as limitCategories
on matches.limitId = limitCategories.limitId

where matches.matchCount = customerCategories.categoryCount
and matches.matchCount = limitCategories.categoryCount

我不知道这是否有效,只是我的一个想法,我不能测试它,我确信有一个更好的方法!不要太苛刻。)

  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
)