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
starhuAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mez4343Commented:
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)
HainKurtSr. System AnalystCommented:
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"
HainKurtSr. System AnalystCommented:
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

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

HainKurtSr. System AnalystCommented:
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


or

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

starhuAuthor Commented:
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
HainKurtSr. System AnalystCommented:
try

SELECT c.*
FROM   customer c  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)

for "in ALL GROUPS" or

SELECT c.*
FROM   customer c  where cust_id in
(SELECT unique cust_id
                   FROM   customer_groups
                   WHERE  group_id IN ( 1, 2, 3 )
)

for "in ANY GROUPS"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.