Mysql – Subselect dependent on first select value

MySQLsubquery

I wish to do a select of all data matching a particular id. I would also like to return a selection of all data matching the alt_id field in the first select.

This example works fine for the main query of two tables and their data based on the contact_id I feed it, in this case '1':

SELECT e_contacts.*, e_credentials.*
FROM e_contacts, e_credentials,
WHERE e_contacts.contact_id = '1' AND 
e_credentials.contact_id = e_contacts.contact_id
ORDER BY e_contacts.contact_id

However, one of the fields in e_contacts is called alt_contact_id (wife or friend) which when holds a value, requires I get both contacts data.

So, I would like to have a second row of data returned for that alt_contact_id's data which exists in the same table. In other words we'll be requerying the same select statement but feeding it e_contacts.contact_id = alt_contact_id found in the first select (if alt_contact_id has a value of course).

This is the table structure:
Table structure

I'm completely uncertain how to accomplish it.

Best Answer

You can use 2 subqueries, one joining with the contact_id and another joining with the alt_contact_id. Then UNION them and ORDER BY:

SELECT cont.*, cred.*, 'main' AS contact_type
FROM e_contacts AS cont
  JOIN e_credentials AS cred
    ON cred.contact_id = cont.contact_id
WHERE cont.contact_id = 1

UNION ALL 

SELECT cont.*, cred.*, 'alternate'
FROM e_contacts AS cont
  JOIN e_credentials AS cred
    ON cred.contact_id = cont.alt_contact_id
WHERE cont.contact_id = 1

ORDER BY contact_id ;

If you want this for all contacts, just remove the two WHERE clauses.

Related Question