I built a SQL Statement, explained here: I have a table Account like this: I have a table Customer like this: I have a relation Customer_To_Account like this: I want to find all accounts which are assigned to customers in a given list like (111111, 222222). The returned accounts MUST contain at least one of the given customers and they

MySQL: Best way finding rows, where associated list match a given list

I built a SQL Statement, explained here:

I have a table Account like this:

|-- Account ---|
| ID | comment |
| 1  | blabla1 |
| 2  | blabla2 |
| 3  | blabla3 |

​x
 
|-- Account ---|| ID | comment || 1  | blabla1 || 2  | blabla2 || 3  | blabla3 |​

I have a table Customer like this:

|---- Customer ----|
|   ID   | comment | 
| 111111 | blabla1 |
| 222222 | blabla2 |
| 333333 | blabla3 |

 
|---- Customer ----||   ID   | comment | | 111111 | blabla1 || 222222 | blabla2 || 333333 | blabla3 |​

I have a relation Customer_To_Account like this:

|----- Customer_To_Account -----|
| ID | account_id | customer_id |
| 1  |     1      |   111111    |
| 2  |     1      |   222222    |
| 3  |     1      |   333333    |
| 4  |     2      |   111111    |
| 5  |     2      |   222222    |
| 6  |     3      |   111111    |

 
|----- Customer_To_Account -----|| ID | account_id | customer_id || 1  |     1      |   111111    || 2  |     1      |   222222    || 3  |     1      |   333333    || 4  |     2      |   111111    || 5  |     2      |   222222    || 6  |     3      |   111111    |​

I want to find all accounts which are assigned to customers in a given list like (111111, 222222). The returned accounts MUST contain at least one of the given customers and they MUST NOT contain a customer that is NOT in the given list.

Expected Result:

For list (111111) the result must be only the account 3, as the account 3 cointains the customer 111111 and NOT contains any other customer. The account 1 and 2 e.g. contain the costumers 222222 which is not in the list (111111) -> so not in the result.
For List (111111, 222222) the result must be account 2 and 3, as they contain at least one of the given customers (111111, 222222) and the NOT contain any other customer. The Account 1 contains the customer 333333, which is not in the list (111111, 222222) -> so not in the result.

My query:

    SELECT * FROM Account WHERE ID IN (
        SELECT distinct account_id FROM Customer_To_Account 
        WHERE customer_id IN("111111", "222222")) -- all accounts, that have one of these customers assigned
      AND ID NOT IN (
        SELECT distinct account_id FROM Customer_To_Account 
        WHERE customer_id NOT IN("111111", "222222")) -- all accounts, that have other customers assigned than the given customers

 
    SELECT * FROM Account WHERE ID IN (        SELECT distinct account_id FROM Customer_To_Account         WHERE customer_id IN("111111", "222222")) -- all accounts, that have one of these customers assigned      AND ID NOT IN (        SELECT distinct account_id FROM Customer_To_Account         WHERE customer_id NOT IN("111111", "222222")) -- all accounts, that have other customers assigned than the given customers​

This returns the correct result, but hasn’t the query a very bad performance, as it selects all IDs of accounts, that have at least one customer, that is not in the given list.

Does anybody has a better idea/query for such a use case?

Thanks a lot!

heiwil

Answer

How about using aggregation?

select account_id
from customer_to_account ca
group by account_id
having sum( customer_id in ( . . . ) ) > 0 and  -- at least 1
       sum( customer_id not in ( . . . ) ) = 0  -- no others

 
select account_idfrom customer_to_account cagroup by account_idhaving sum( customer_id in ( . . . ) ) > 0 and  -- at least 1       sum( customer_id not in ( . . . ) ) = 0  -- no others​

Advertisement

Answer