[SOLVED] Complex SELECT (for me, at least)

[Luth]

I'm making a heavy duty mod to PHPBB, and it's pushing my limited knowledge of MySQL (5.0).  What I want is to select:

1) everything from phpbb_topics

[phpbb_topics.*],
2) the user email associated with the first poster [phpbb_users.user_email as first_poster_email],
3) the user email associated with the last poster [phpbb_users.user_email as last_poster_email].

The closest thing I've got is:

SELECT t.*, phpbb_users.user_email as first_poster_email
FROM (
phpbb_topics t
)
LEFT JOIN phpbb_users
ON t.topic_poster=phpbb_users.user_id

 

Obviously it's lacking, as there's not even a bit for selecting the last poster email.  The user id's are stored in phpbb_topics as 'topic_poster' and 'topic_last_poster_id'.

 

I'm not sure how to proceed with this.  Any help would be nice, or even just pointers in the right direction.

 

Cheers. 

[Luth]

Is there any more information that I could provided that would assist you in assisting me with this hang-up?  I'm afraid I haven't yet found a solution elsewhere, either.

 

Kind regards.

[fenway]

Try doing (2) and (3) independently.

[Luth]

That is what I've done, and done an array merge in PHP to combine the results.  If there is no way to do them in one operation, then I guess that's as good as it gets, hm?    Thank you.

[roopurt18]

select
  a.*,
  (select user_email from phpbb_users b where a.topic_poster=b.user_id) as first_poster_email,
  (select user_email from phpbb_users c where a.topic_last_poster_id=c.user_id) as last_poster_email
from phpbb_topics a

 

Or maybe:

select
  a.*,
  b.user_email as first_poster_email,
  ifnull( c.user_email, b.user_email ) as last_poster_email
from phpbb_topics a
inner join phpbb_users b on a.topic_poster=b.user_id
left join phpbb_users c on a.last_poster_id=c.user_id

[Luth]

Wow.  Okay, I could follow the first one, but that second one throws me for a loop.  :-)  At any rate, I'll try them and get back to you.

 

Cheers.

[kickstart]

Hi

 

Not sure which version of phpBB you are using, but assuming 2 (which I am still using) then the topics table contains the post_id of the first and last posts, so you can JOIN to the users table via the post table.

 

Something like this:-

 

SELECT a.*, c.user_email as first_poster_email, e.user_email as last_poster_email
FROM phpbb_topics a
JOIN phpbb_posts b ON a.topic_first_post_id = b.post_id
JOIN phpbb_users c ON b.poster_id = c.user_id
JOIN phpbb_posts d ON a.topic_last_post_id = d.post_id
JOIN phpbb_users e ON d.poster_id = e.user_id

 

All the best

 

Keith

[Luth]

Both Kickstart and Roopurt's first select statements worked peachy.  Thanks much.