Multiple joins explained
I’ll try to explain how a join on more than one table works. I’ve noticed people get confused by it. Assume we have the following tables
We want to display for each newsitem the content and the author.
Our base table would be the newsitems, and then we join using the item_id. Thus the query would be
SELECT * FROM newsitems INNER JOIN ON postitems USING (post_id)
This returns a “virtual table” that has looks like this result(news_id,post_id,user_id,content).
Now we still need to get the username, so we use our result table and perform a join on the users table. Thus the query would be
SELECT * FROM result INNER JOIN users USING (user_id)
If we combine our first two queries, we end up with this
SELECT * FROM newsitems INNER JOIN postitems USING (post_id) INNER JOIN users USING (user_id)
Conclusion: Look at ABC as (A*B)*C to easily understand multiple joins