🔒 Locked 🤖 Godot

[web] MySQL multiple JOIN query

Started by Bessi Nov 17, 2004 at 1:19 PM 2 replies 3.6k views

Original Post

Nov 17, 2004 at 1:19 PM

I'm currently writing a forum for a webpage. I have two tables, one with the forum posts and one with the users. The forum table contain two userid's for each post (the sender-of-the-last-answer userid and the original-poster userid) and I want to find out the nick of those userid's with a join query. Currently I'm using SELECT * FROM Eik_Forum LEFT JOIN Eik_Users ON (Eik_Forum.originalid = Eik_Users.id) and with that I can get the nich of the original poster with mysql_fetch_array and $nick = $row['nick'];. But the problem is that I also want to get the nick of the sender of the last answer in the same query. I have no clue how to do that since that would mean that the query would return two colums from the Eik_Users table both called 'nick'. Is it possible to solve that problem? Or do I have to do some redesigning?

Sander

Nov 18, 2004 at 3:00 AM

Not sure if the sytax is 100% correct but it should be something like this (op = original poster, lp = last poster):

SELECT f.*, u1.username AS op, u2.username AS lp FROM Eik_Forum f LEFT JOIN Eik_Users AS u1 ON (f.originalid = u1.id) LEFT JOIN Eik_Users AS u2 ON (f.lastid = u2.id)

You can join the same table as many times as you want as long as you give them different names each time so just rename variables and tables with the keyword AS.

Zul

Nov 18, 2004 at 9:24 AM

Use Sander's query, but you may want to use Right instead of Left Join. The Left Join could potentially bring back a much larger record set if a user responded to another user's post multiple times. The right join should only return two records I think.

You could also use a UNION for this.

SELECT f.*, u1.username
FROM Eik_Forum f
LEFT JOIN Eik_Users AS u1 ON (f.originalid = u1.id)
UNION
SELECT f.*, u2.username
FROM Eik_Forum f
LEFT JOIN Eik_Users AS u2 ON (f.lastid = u2.id)

But it's a gonna be a matter of preference. Oh, you'll have to use the same column name for both names in a UNION, so maybe you don't want to use it afterall.

Bessi

Nov 18, 2004 at 12:55 PM

Thank you, Sander and Zul.

That was exactly what I needed.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.