Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
I have updated the image associated with this at the same address: compumatter.com/subquery.png –  CompuMatter Aug 19 '13 at 2:59
add comment

1 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.