SQL searching for rows that contain multiple criteria

Question

I have 3 tables

Customer
Groups
CustomerGroupJoins

Fields to be used

Customer:Key
Groups:Key
CustomerGroupJoins:KeyCustomer, KeyGroup

I need to search for all users that are in all groups with keys, 1,2,3

I was thinking something like (but have no idea whether this is the right/best way to go):

SELECT 
  * 
FROM 
  Customer 
WHERE 
  Key = (
    SELECT KeyCustomer 
    FROM   CustomerGroupJoins 
    WHERE  KeyGroup = a
  ) = (
    SELECT KeyCustomer 
    FROM   CustomerGroupJoins 
    WHERE  KeyGroup = b
  ) = (
    SELECT KeyCustomer 
    FROM   CustomerGroupJoins 
    WHERE  KeyGroup = c
  )

Answer 1

Here's a possible answer, not tested:

select custid
from CustomerGroupJoins
where groupid in (1,2,3)
group by custid
having count(*) = 3

Searches for customer's that have 3 rows with groupid 1, 2, or 3. Which means that they are in all 3 groups, because I assume you have a primary key on (custid,groupid).

Answer 2

The above solutions will work if the customer is in any of the three groups, but won't check for membership in all of them.

Try this instead:

SELECT  a.*
FROM    (SELECT c.*, substring((SELECT      (', ' + cg.KeyGroup)
                                FROM        CustomerGroupJoins cg
                                WHERE       cg.KeyCustomer = c.[Key]
                                AND         cg.KeyGroup IN (1,2,3)
                                ORDER BY    cg.KeyGroup ASC
                                FOR XML PATH('')), 3, 2000) AS GroupList
        FROM    Customer AS c) AS a
WHERE   a.GroupList = ('1, 2, 3')

This will also work:

SELECT  c.*
FROM    Customer c
WHERE   c.[Key] IN  (SELECT     cg.[KeyGroup]
                    JOIN        CustomerGroupJoins cg                       WHERE       cg.KeyGroup IN (1,2,3)
                    GROUP BY    cg.KeyGroup
                    HAVING      count(*) = 3)

Answer 3

I created this test data:

srh@srh@[local] =# select * from customer join customergroupjoins on customer.key = customergroupjoins.keycustomer join groups on groups.key = customergroupjoins.keygroup;
 key |  name  | keycustomer | keygroup | key |  name   
-----+--------+-------------+----------+-----+---------
   1 | fred   |           1 |        1 |   1 | alpha
   1 | fred   |           1 |        2 |   2 | beta
   1 | fred   |           1 |        3 |   3 | gamma
   2 | jim    |           2 |        1 |   1 | alpha
   2 | jim    |           2 |        2 |   2 | beta
   2 | jim    |           2 |        4 |   4 | delta
   2 | jim    |           2 |        5 |   5 | epsilon
   3 | shelia |           3 |        1 |   1 | alpha
   3 | shelia |           3 |        3 |   3 | gamma
   3 | shelia |           3 |        5 |   5 | epsilon
(10 rows)

So "fred" is the only customer in all of (alpha, beta, gamma). To determine that:

srh@srh@[local] =# select * from customer
  where exists (select 1 from customergroupjoins where keycustomer = customer.key and keygroup = 1)
  and exists (select 1 from customergroupjoins where keycustomer = customer.key and keygroup = 2)
  and exists (select 1 from customergroupjoins where keycustomer = customer.key and keygroup = 3);
 key | name 
-----+------
   1 | fred
(1 row)

This is one approach. The (1,2,3) - your known group keys - are the parameters in the subqueries. Someone already mentioned you don't actually need to join to the groups table at all.

Another way:

select customer.*
from customer
  join customergroupjoins g1 on g1.keycustomer = customer.key
  join customergroupjoins g2 on g2.keycustomer = customer.key
  join customergroupjoins g3 on g3.keycustomer = customer.key
where g1.keygroup = 1 and g2.keygroup = 2 and g3.keygroup = 3

The general problem of finding users with all groups (g_1, g_2 .. g_N) is a bit tricker. These queries above have joined to the link table (customergroupjoins) N times, so it's a different query depending on the number of groups you're checking against.

One approach to that is to create a temporary table to use as a query parameter: the table contains the list of groups that the customers must have all of. So for instance create a temp table called "ParamGroups" (or "#ParamGroups" on SQL Server to mark it as temporary), populate it with the group keys you're interested in and then do this:

select * from customer where key in (
    select keycustomer
    from customergroupjoins
      join paramgroup on paramgroup.keygroup = customergroupjoins.keygroup
    group by keycustomer
    having count(*) = (select count(*) from paramgroup))

Also, as a beginner, I strongly recommend you look into advice about naming conventions for database tables and columns. Everyone has different ideas (and they can spark off holy wars), but pick some standards (if they aren't dictated to you) and stick to them. For instance you named one table "customer" (singular) and one table "groups" (plural) which looks bad. It's more usual to use "id" rather than "key", and to use it as a suffix ("customer_id" or "CustomerID") than a prefix. The whole CamelCase vs old_skool argument is more a matter of style, as is the primary-key-is-just-"id"-not-"table_id".

Answer 4

SELECT *
  FROM customer c
    INNER JOIN customerGroupJoins j ON(j.customerKey = c.key)
  WHERE j.keyGroup IN (1, 2, 3)

You don't need to join against groups-table, as long as you are only interested in the group key, which is found in your join table.

Answer 5

From what you described, try this:

SELECT * FROM Customer c
INNER JOIN CustomerGroupJoins cgj
ON c.key = cgj.keyCustomer
INNER JOIN groups g
ON cgj.keyGroup = g.key
WHERE g.key IN (1,2,3)

Answer 6

Maybe something like this?

SELECT c.Key, g.Key, cgj.KeyCustomer, cgj.KeyGroup
FROM Customer c
LEFT JOIN CustomerGroupJoins cgj ON cgj.KeyCustomer = c.Key
LEFT JOIN Groups g ON g.Key = cgj.KeyGroup
WHERE g.key IN (1, 2, 3)