Flatten subselect rows for WHERE IN

Question

I have a table of customers in which there are 3 columns for ids for different accounts, like this:

customers: id, account1_id, account2_id, account3_id

Now, I need to select ids from all those columns for use in WHERE part of another query, like this:

SELECT * FROM balances
WHERE account_id IN (
    SELECT account1_id, account2_id, account3_id FROM customers
)

Of course it doesn't work because it returns something like 2-dimentional array and I need 1-dimentional one. I tried to use:

WHERE (`account_id`, `account_id`, `account_id`) IN (
    SELECT account1_id, account2_id, account3_id FROM customers
)

but it ANDs account_id and I need ORs. Also tried:

SELECT CONCAT_WS(',' account1_id, account2_id, account3_id) AS accounts FROM customers

but that it doesn't work too as I had to search inside returned strings.

I tried to use REGEXP but couldn't get right syntax with subselect and also I'm not convinced it would be efficient.

So, how do I flatten that 2d array into 1d one?

Answer 1

SELECT balances.* 
FROM balances
JOIN customers ON balances.account_id IN ( customers.account1_id, 
                                           customers.account2_id, 
                                           customers.account3_id )

or the same with WHERE EXISTS.