0

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
Community
  • 1
  • 1
adam0101
  • 29,096
  • 21
  • 96
  • 174

3 Answers3

0

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
Nathan Wheeler
  • 5,896
  • 2
  • 29
  • 48
  • I believe this will return records where ANY customer categories match limit categories. I need ALL categories to match. Thanks anyway. – adam0101 Dec 30 '09 at 22:46
  • Edited to select ALL CustomerCategories and leave nulls where customers or limits don't exist... is this what you want? – Nathan Wheeler Dec 30 '09 at 22:50
  • No, I believe this will still return records if, for example, a limit has two of the three categories a customer has. I need the customer's group of categories to match the limit's group of categories exactly. – adam0101 Dec 30 '09 at 22:59
0

Updated!

Thanks to Felix for pointing out a flaw in my existing solution (3 years after I originally posted it, hehe). After looking at it again, I think this might be correct. Here I'm getting (1) the customers and limits with matching categories, plus the number of matching categories, (2) the number of categories per customer, (3) the number of categories per limit, (4) I then ensure the number of categories for customer and limits is the same as the number of the matches between the customers and limits:

UNTESTED!

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
adam0101
  • 29,096
  • 21
  • 96
  • 174
  • 1
    Adam, I don't think your Tested solution is exactly what you're looking for, as it can return a pairing as long as ONE of the child records match, and the overall counts match. – Felix Cartwright Nov 29 '12 at 00:45
  • @FelixCartwright, wow you're right. I think both solutions have that problem. I updated my answer. Thanks. – adam0101 Dec 03 '12 at 18:50
-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
)
Paul Creasey
  • 28,321
  • 10
  • 54
  • 90
  • thanks, but I don't know what would go in the "ON" clause of the inner join. There is no link between customers and limits other than the categories. I updated the original question to include schema info. – adam0101 Dec 30 '09 at 22:48
  • Edited it to a cross join, as i said, i can't test it, but it seems like it should work to me! Hope it doesn't need to be efficient though! – Paul Creasey Dec 30 '09 at 22:56
  • Why would customerId have to be greater than limitId? Can you explain this part? "c.customerId > l.limitId" – adam0101 Dec 30 '09 at 23:03
  • ah sorry, that is an error, that is only needed when doing a self cross join to remove duplicates, edited! – Paul Creasey Dec 30 '09 at 23:08
  • It looks like it would work, but it does need to be efficient. The customer table is HUGE. Would it still work and be more efficient if I changed NOT EXISTS to EXISTS and EXCEPT to INTERSECT? – adam0101 Dec 30 '09 at 23:14
  • This won't work either way. It returns too many records. I believe it cross joins each matching customer to each matching limit. – adam0101 Dec 31 '09 at 03:42
  • Just play around with the where clause untill it works, i'm convinced it will! – Paul Creasey Dec 31 '09 at 07:51
  • No, there's another reason this won't work. If a limit has, for example, two of the three categories a customer has you'd get a false positive. – adam0101 Dec 31 '09 at 14:03
  • query1 EXCEPT query2, will only NOT EXIST if query and query 2 return the exact same set of results, which will only happen if all categories match. – Paul Creasey Dec 31 '09 at 15:30
  • No, if query2 has 3 records and query1 matches 2 of those 3 records it will NOT EXIST because you are saying give me the 2 records from query1 EXCEPT where they exist in query2 which would produce zero records. The only way this would work is if you created an additional NOT EXISTS statement and switched the order of the queries. Even so, I don't think this would produce what you'd want because it would still cross join all the customers to all the limits. Each customer should only have one limit. – adam0101 Dec 31 '09 at 15:55