I have 3 tablesCustomer Groups CustomerGroupJoins
Fields to be usedCustomer: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 )Answer1:
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.
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 <em>that</em> 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".Answer2:
The above solutions will work if the customer is in <em>any</em> of the three groups, but won't check for membership in <em>all</em> 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)Answer3:
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)Answer4:
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)Answer5:
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.Answer6:
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).