CODEWITHSUNDEEP
mysqljoinalias
j__carlson
j__carlson

Reputation: 1348

MYSQL joining based on two different parameters

My Code:

SELECT eval_table.form, teacher_table.name AS Subject, teacher_table.name0 AS Author  
FROM `eval_table`
JOIN `teacher_table` ON eval_table.subj = teacher_table.user_id;

Result:

form Subject Author
internal review John Doe John Doe

My problem is that I need to simultaneously JOIN teacher_table ON eval_table.auth = teacher_table.user_id; but only for teacher_table.name0 to get the following result:

form Subject Author
internal review John Doe Jane Doe

teacher_table.name0 is a virtual copy of teacher_table.name

Upvotes: 0

Views: 33

Answers (2)

Matthias Winkelmann
Matthias Winkelmann

Reputation: 16394

You want to alias the table, allowing you to work with it as if it were a different one:

SELECT evaluations.form, teachers.name, evaluators.name 
FROM evaluations 
JOIN teachers 
  ON evaluations.subject = teachers.name
JOIN teachers AS evaluators 
  ON evaluations.evaluator = evaluators.name;

As an aside, I’ve taken the liberty to slightly standardize the naming scheme. Use plurals or singulars, capitalize or don’t, but keep it consistent. And tables don’t need _table suffixes. They know what they are.

Upvotes: 0

Martin
Martin

Reputation: 16433

You could make a second join to the teacher_table and then return the result as indicated.

The following SQL gives an example of this:

SELECT  et.form,
        tt1.name AS Subject,
        tt2.name0 AS Author  
  FROM  eval_table et
    INNER JOIN teacher_table tt1 ON tt1.user_id = et.subj
    INNER JOIN teacher_table tt2 ON tt2.user_id = et.auth;

In this example, the first join to teacher_table is on user_id = et.subj as per the existing join and uses the alias tt1. The second join is on user_id = et.auth and uses the alias tt2.

Using the method above you can make multiple joins to the same table on different columns. Using an alias for the table names (i.e. et, tt1, and tt2) makes it possible to use the same table more than once but refer to them uniquely in the output.

Note that the SQL is untested but should give an indication as to how to proceed.

Upvotes: 1

Related Questions