CodeIgniter Forums
SQL Question - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: SQL Question (/thread-20908.html)



SQL Question - El Forum - 07-25-2009

[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.


SQL Question - El Forum - 07-25-2009

[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.


SQL Question - El Forum - 07-25-2009

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


SQL Question - El Forum - 07-25-2009

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


SQL Question - El Forum - 07-25-2009

[eluser]frbry[/eluser]
Hahah Smile

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.


SQL Question - El Forum - 07-25-2009

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

Well, now you know Big Grin