* John Hughes
> I have three tables:
>
> students has student_id and student_name
> parents has parent_id and parent_name
> parentlog has student_id and parent_id
>
> I want to search the parentlog WHERE student_id = some_id
> GROUP BY parent_id
>
> (This will bring back two rows when there are two parents)
>
> At the same time I want to get the name of the student that
> matches student_id and the name of the parent.
>
> I can LEFT JOIN students with parentlog USING(student_id) but
> I can't figure how I can join the parents so that I can get
> the name of match for the parent_id.
>
> Can I join three tables and search all in one pass?
Yes, and LEFT JOIN may not be needed:
SELECT students.*,parents.*
FROM parentlog,students,parents
WHERE
students.student_id = parentlog.student_id AND
parents.parent_id = parentlog.parent_id
Using LEFT JOIN:
SELECT students.*,parents.*
FROM parentlog
LEFT JOIN students USING(student_id)
LEFT JOIN parents ON
parents.parent_id = parentlog.parent_id
USING can not be used in the second join because it relates to the previous
table, which in this case is students, and that table has no parent_id. By
using ON you can join with any table in your table list.
More details can be found in the manual:
<URL: http://www.mysql.com/doc/J/O/JOIN.html >
--
Roger
query
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
