MySQL JOIN: Multiple fields same name

Question

I'm no pro in SQL but I'm playing around with the following SELECT:

SELECT 
        certificate.id, 
        certificate.date_created,
        certificate.date_approved,
        certificate.notes,
        certificate.id_user_student,
        certificate.id_user_exm,
        certificate.id_user_eval,

        user.name AS student_name,
        user.name AS exm_name,
        user.name AS eval_name

        FROM certificate
        LEFT JOIN 
            user ON certificate.id_user_student = user.id AND
                    certificate.id_user_exm = user.id AND
                    certificate.id_user_eval = user.id

My ideia with this is to be able to take the name column from the table user and JOIN it into the certificate table for all user id fields placed.

In the table certificate the columns id_user_student, id_user_exm, id_user_eval contain user Ids available on the table user and I want to their names on the result set.

My current query only works fine for the first result. Can anyone tell me how can I fix this?

Thank you!

INNER JOIN? Also, not sure why the ID has to match all three id_user_student, id_user_exm, id_user_eval....consider normalizing the certificate table so that your ON condition would just be matching one primary key with one foreign key.... — A.O.
– A.O., Commented Jul 12, 2013 at 19:48
@A.O. I think the OP wants a separate name lookup for each of the three foreign key columns. — spencer7593
– spencer7593, Commented Jul 12, 2013 at 19:49
@spencer7593 you're right, because all the students, emxs and evals are people on the user table and all them are involved in the certification process. ;) — TCB13
– TCB13, Commented Jul 12, 2013 at 23:14

spencer7593 · Accepted Answer · 2013-07-12 19:49:01Z

3

You'll need three join operations, one for each "lookup" of a name based on id. You'll need three references to the user table, one for each foreign key reference, for example:

SELECT c.id
     , c.date_created
     , c.date_approved
     , c.notes
     , c.id_user_student
     , c.id_user_exm
     , c.id_user_eval
     , s.name AS student_name
     , e.name AS exm_name
     , v.name AS eval_name
  FROM certificate c
  LEFT
  JOIN user s ON s.id = c.id_user_student
  LEFT
  JOIN user e ON e.id = c.id_user_exm
  LEFT
  JOIN user v ON v.id = c.id_user_eval

answered Jul 12, 2013 at 19:49

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL JOIN: Multiple fields same name

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related