+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: SQL Question (/showthread.php?tid=20908)
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.idSomething 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
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
