asked on

Mysql select all companies when all groups are true

Hello,

I have the following 2 tables (I simplified it here):

Customer:
`cust_id` int(10) unsigned NOT NULL auto_increment
`cust_name` varchar(100) default NULL

Customer_groups -> a customer can belong to many groups at the same time
`cust_id` int(10) unsigned NOT NULL
`group_id` int(10) unsigned NOT NULL

I need to select those customers who belong to ALL the selected groups.

For example let's say that the Customer_groups would have:

cust_id group_id
1 1
1 2
2 1
2 2
2 3
3 3
3 4
4 12
5 1
5 2
5 3
5 9
6 9

I want to select only those customers who belongs to group 1, group 2 and group 3.

So the result would be: customer 2 and customer 5.

How can I make such a query?

Thank you

Mez4343

Based on your sample data the result would be Customers 1,2,3 and 5 since they belong to groups(1,2 or 3). I assume you only want the query result to return the Cust_id if the cust is in the list of groups, so added the distinct keyword. Try this

SELECT DISTINCT customer
FROM Customer a, Customer_groups b
WHERE a.cust_id = b.cust_id
AND b.group_Id in (1,2,3)

HainKurt

try this:

SELECT * 
FROM   customer 
WHERE  cust_id IN (SELECT cust_id 
                   FROM   customer_groups 
                   WHERE  group_id IN ( 1, 2, 3 ) 
                   GROUP  BY cust_id 
                   HAVING COUNT(1) = 3)

Open in new window

if it works we can write a better performing query with joins instead of using "in"

HainKurt

like this

SELECT c.* 
FROM   customer c 
       INNER JOIN (SELECT cust_id 
                   FROM   customer_groups 
                   WHERE  group_id IN ( 1, 2, 3 ) 
                   GROUP  BY cust_id 
                   HAVING COUNT(1) = 3) cg 
         ON cg.cust_id = cg.cust_id

Open in new window

HainKurt

oops, my solutions are to find customers who belongs to ALL not ANY :)

just remove HAVING COUNT(1) = 3 from above query

SELECT c.* 
FROM   customer c 
       INNER JOIN (SELECT cust_id 
                   FROM   customer_groups 
                   WHERE  group_id IN ( 1, 2, 3 ) 
                   GROUP  BY cust_id) cg 
         ON cg.cust_id = cg.cust_id

Open in new window

SELECT c.* 
FROM   customer c 
       INNER JOIN (SELECT unique cust_id 
                   FROM   customer_groups 
                   WHERE  group_id IN ( 1, 2, 3 )) cg 
         ON cg.cust_id = cg.cust_id

Open in new window

starhu

ASKER

This works :

SELECT c.* 
FROM   customer c 
       INNER JOIN (SELECT cust_id 
                   FROM   customer_groups 
                   WHERE  group_id IN ( 1, 2, 3 ) 
                   GROUP  BY cust_id 
                   HAVING COUNT(1) = 3) cg 
         ON cg.cust_id = cg.cust_id

Open in new window

... but can you somehow transform the select in such a form:
SELECT c.*
FROM customer c
where ....

The customer table is an editable data grid in a Windows application, and the commercial Mysql plugin behaves erratically when there is a join in the select (the internal update mechanism will try to update the other table as well).

Thank you in advance

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial