I need to do a query between these 3 tables : members, messages and schools.
In my table "messages", I save the member ID (to know who wrote the message). In my table "members", I save the school ID of the member.
But I would like to create something like a Facebook wall, where you could see a list of all the messages from anyone who's in your school.
It's easy for me to get all the posts from anybody, but I can't figure out how to get only the posts from people in your school. I save the school ID of the member in a session so it's easy to know from which school we need to get the posts.
Is it possible to do this in one query?
Thanks !
Here is the SQL query:
SELECT msg.*
FROM messages AS msg
INNER JOIN members AS mem ON msg.member_id = mem.id
INNER JOIN schools AS sch ON mem.school_id = sch.id
WHERE sch.id = ?
As Michael suggested in the comments, here is an article that explains SQL joins visually: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
SELECT m.message
FROM messages m
JOIN members b
ON m.memberid = b.memberid
JOIN schools s
ON s.schoolid = b.schoolid
WHERE s.schoolid = "replace by the schoolid stored in session"