Jump to content
Larry Ullman's Book Forums

Multiple Concat In One Select Query


Recommended Posts

 

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.

Link to comment
Share on other sites

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.

  • Upvote 1
Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...