0
|  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?

4
  • Are we actually talking about families or is that a stand-in for your actual problem?
    – bbaird
    Jul 23 '20 at 21:36
  • its just a stand in because I copied and pasted
    – cubesnyc
    Jul 23 '20 at 21:40
  • Ok, because that would be a really bad way to actually model families. Do you want your output on one row or multiple rows?
    – bbaird
    Jul 23 '20 at 21:42
  • I am not sure how it would be displayed on multiple rows.
    – cubesnyc
    Jul 23 '20 at 21:44
0

You have would have to join twice:

SELECT
  fam.family_id
 ,fam.parent_id_1
 ,parent1.address_id AS parent_1_address_id
 ,parent1.name AS parent_1_name
 ,parent1.job_id AS parent_1_job_id
 ,fam.parent_id_2
 ,parent2.address_id AS parent_2_address_id
 ,parent2.name AS parent_2_name
 ,parent2.job_id AS parent_2_job_id
FROM
  family fam
LEFT JOIN
  person parent1
    ON parent1.person_id = fam.parent_id_1
LEFT JOIN
  person parent2
    ON parent2.person_id = fam.parent_id_2
LEFT JOIN
  job job1
    ON parent1.job_id = job1.job_id
<... over and over ... >

You can use a common table expression to wrap some of the identical code together, but it's the same steps ultimately:

WITH person_detail AS
(
  SELECT
    person.person_id
   ,person.name
   ,<job columns>
   ,<address columns>
  FROM
    person person
  LEFT JOIN
    job job
      ON job.job_id = person.job_id
  LEFT JOIN
    address address
     ON address.address_id = person.address_id
)
SELECT
  fam.family_id
 ,fam.parent_id_1
 ,<parent_1 columns>
 ,fam.parent_id_2
 ,<parent_2 columns>
FROM
  family fam
LEFT JOIN
  person_detail parent1
    ON parent1.person_id = fam.parent_id_1
LEFT JOIN
  person_detail parent2
    ON parent2.person_id = fam.parent_id_2
4
  • this works great, but it doesnt join the jobs and addresses onto the job_id and address_id columsn for each person
    – cubesnyc
    Jul 23 '20 at 21:51
  • You don't have those listed in your question so I have no idea what columns you'd be adding or why.
    – bbaird
    Jul 23 '20 at 22:18
  • to clarify, i always have to enumerate the exact columns I want to select along with their table aliases to prevent ambiguity, is that right? Also, is the common table expression query more efficient? And lastly, in your first example, would I have to left join job and address each twice as well?
    – cubesnyc
    Jul 23 '20 at 22:28
  • 1. Yes, it is a good idea to include a reasonable table alias when you list the columns so it's easy to discern a column's source. 2. The CTE is less code, but depending on a few factors it could be slower. 3. Yes, if you choose the first method you would need join twice for each table of interest.
    – bbaird
    Jul 24 '20 at 2:16
0

Parentheses are allowed:

FROM ( a JOIN b ON ... )
JOIN ( c JOIN d ON ... ) ON ...

(etc)

This can be especially useful when using RIGHT JOIN or LEFT JOIN -- it will help clarify your intention without having to figure out the "precedence" rules.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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