Multiple Concat In One Select Query

[fumble]

 

Hi all.

 

I have designed a forum ( with great help from this book - thanks Larry ) which seems to be working well. 

 

One thing still bugs me though. 

 

I have a table for forum threads which contains among others, the following two colums:

thread_originator_id and last_reply_poster_name.

 

Ideally I would like to have both columns contain each forum user's ID ( numeric ).  However, I can't write the Query ( believe me, I tried - for days! ) which returns both users names ( using a join between two tables - users and threads_forum ).  CONCATing one user is easy, but two ??  

 

As a temp fix, I turned the last_reply_poster_name column into a varchar column and stored the person's name, which still gives me sleepless nights - horrible programming!

 

My code is like this.

 

$q3 = "SELECT  CONCAT(first_name,' ',last_name) AS thread_starter_name, thread_title, thread_main_forum_id, 

DATE_FORMAT(date_thread_started, '%a, %e %b %y, %H:%i') AS date_thread_started,  DATE_FORMAT(time_last_reply, '%a, %e %b %y, %H:%i ') AS time_last_reply, 

num_replies, thread_originator_id, last_reply_member_id, last_reply_poster_name, last_reply_post_id

 

FROM threads_forum, users 

WHERE forum_sections_id = '$sid' AND group_id = '$gid' AND user_id = thread_originator_id

 

ORDER BY last_reply_post_id DESC 

"; 

 

 

So, I would like to be able to write a query which CONCATS both names using the two user IDs -  thread_originator_id  and  last_post_reply_user_id.

 

( There are other ways in which to solve the problem - redesigning the tables for one, but I'm interested in a properly written query to the above problem. )

 

Many thanks in advance.

[Antonio Conte]

What you need to do is joining the tables several times. I'll illustrate:

SELECT threads.*,
originator.user_id AS originator_id,
originator.name AS originator_name,
last_poster.user_id AS last_id,
last_poster.name AS last_name

FROM threads_forum AS threads
INNER JOIN users AS orginator ON (threads.thread_originator_id = orginator.user_id)
INNER JOIN users AS last_poster ON (threads.last_reply_member_id = last_poster.user_id)

I might have used wrong column names, but that's the logic you have to use. Notice the aliasing on the joins. That's the key on how to solve such problems.

[fumble]

Thank you Antonio.

 

I will have to revisit my understanding - or lack thereof ! - of joins.  I have managed to date using the AND expression to join tables, but your example is the way forward.  I'll have a go at rewriting my query.

 

Really appreciate the help.