| family_id | parent_id_1 | parent_id_2 |
-----------------------------------------------
| 1234 | 9999 | 9567 |
-----------------------------------------------
| person_id| address_id | name | job _id |
-----------------------------------------------------------
| 1234 | 3 | ABCD | 5 |
-----------------------------------------------------------
| 9999 | 4 | ABCD | 3 |
-----------------------------------------------------------
| job_id | title | start_date |
-----------------------------------------------
| 3 | asdf| 3-2-2001|
-----------------------------------------------
| address_id | street | zip |
-----------------------------------------------
| 3 | 1 main | 11234 |
-----------------------------------------------
I have copy and pasted a database schema from a similar question and made some minor modifications. In mysql I have several tables: Family, Person, Address, Job. Family has two columns that reference a person. A person has a column that references an Address, and one column that references a job. I would like query a family so that I can see both persons with their respective jobs and addresses. What is the correct way to accomplish that?
Best Answer
You have would have to join twice:
You can use a common table expression to wrap some of the identical code together, but it's the same steps ultimately: