Search for an "exotic" value in a MySQL table

I have a MySQL table called accounts. Inside this table is a field called salesmanager_id. In 99% of cases, the value in this field is always the same for all accounts of a particular client (indicated by customer_id).

What I'm trying to do is find customer_idfor customers who have accounts assigned to more than one sales manager. For instance:

+------------------------------------+
| id | customer_id | salesmanager_id |
|------------------------------------|
| 1  | 12          | 4               |
| 2  | 12          | 4               |
| 3  | 14          | 3               | <-- I want this customer_id
| 4  | 14          | 4               |
+------------------------------------+

In the above example, customer_id14 has both salesmanager_id3 and 4 assigned to it. I would like to get this one customer_idfor my list.

I tried the following query, but returns an empty result (although I'm sure there are at least some differences).

SELECT `name`, `customer_id` AS `customer`, `salesmanager_id` FROM `accounts`
WHERE `salesmanager_id` NOT IN (
    SELECT `salesmanager_id` FROM `accounts` a
    LEFT JOIN `customers` c ON (a.customer_id = c.id)
    WHERE a.customer_id=c.id
) GROUP BY `customer`;
+5
source share
1
SELECT 
    customer_id
FROM 
    accounts
GROUP BY 
    customer_id
HAVING 
    COUNT(DISTINCT salesmanager_id) > 1

salesmanager_id customer_id salesmanager_id, customer_id.

+13

All Articles