Need SQL to get a record with two corresponding entries

Question

Hi guys, I have a table which holds contact details against corresponding account numbers:

Contacts_Accounts Table

contact id | account ID
=======================
1          | 12
1          | 13
1          | 14
2          | 12
2          | 13
3          | 12

how can I make a sql query that returns all entries that have BOTH account numbers 12 and 13 for example ...I'm using MYSQL here

Answer 1

This nested query should do the trick:

SELECT * FROM Contacts_Accounts where account_ID = 12 and contact_id in (SELECT contact_id from Contacts_Accounts where account_ID = 13)

Answer 2

SELECT 
  contact_id
FROM
  contacts_accounts
WHERE 
  account_id in (12,13)
GROUP BY 
  contact_id
HAVING count(account_id) = 2

Answer 3

Or you can use group_concat which will return the account_id in comma separated list.

SELECT contact_id, group_concat(account_id) FROM contacts_accounts GROUP BY contact_id;

Not sure if that's what you're after but group_concat is quicker than nested queries I've found and there are more args you can parse to group_concat if the above query doesn't meet your requirements.