SQL Question

[eluser]frbry[/eluser]
Hi,

I'm using DX_Auth for authorization in my web application. So, all the users are in "users" table. I have another table called lessons that has three columns: id | student_id | teacher_id. Both student_id and teacher_id are just ids from the users table because both student and teacher are members of the site.

I think this is a problematic database design for me because i can't get the student's name and the teacher's name at the same row when i want to get all the lessons in the table.

How should i proceed from this point?

Thank you.

[eluser]davidbehler[/eluser]

Code:

SELECT ustudent.name as student_name, uteacher.name as teacher_name FROM lessons left join users ustudent on lessons.student_id = ustudent.id left join users uteacher on lessons.teacher_id = uteacher.id

Something like that should work. You might have to adjust the column names as I don't know the layout of your users table.

[eluser]frbry[/eluser]
Thank you man! Didn't know that JOIN can be used that way.

[eluser]davidbehler[/eluser]
In what other way should it be used? ^^

[eluser]frbry[/eluser]
Hahah

I was using it only this way:

SELECT lessons.start_date, users.name FROM lessons LEFT JOIN users ON lessons.user_id = users.id

So, i didn't know that i could use "identifiers" (?) along the JOIN syntax.

[eluser]davidbehler[/eluser]
Ah, I see.

Well, now you know