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`;
source
share