Selecting first and last message subject.

[JayRoe]

While it's possible to get both smf_topics.id_first_msg and smf_topics.id_last_msg in the same query, is it possible to get both subject names in the same query?

I have a query that searches for messages with the subject [review] and I'd like the query to return the subject of the first message in the topic as well.

[rsw686]

Here you go.

SELECT t.id_topic, f.subject AS first_subject, l.subject AS last_subject
FROM smf_topics AS t
LEFT JOIN smf_messages AS f
ON t.id_first_msg = f.id_msg
LEFT JOIN smf_messages AS l
ON t.id_last_msg = l.id_msg

[rsw686]

Heres the SQL joining the above in with your other post on the subject with [review]. Change the f in poster_time and poster_time to l if you would like the information from the last poster. Or grab both by doing it like I did for the subject.

SELECT t.id_topic, t.id_board, f.poster_time, f.poster_name,
   f.subject AS first_subject, l.subject AS last_subject
FROM smf_topics AS t
LEFT JOIN smf_messages AS f
   ON t.id_first_msg = f.id_msg
LEFT JOIN smf_messages AS l
   ON t.id_last_msg = l.id_msg
WHERE t.id_board IN (' . $use_boards .')
   AND subject LIKE '%[review]%'

[JayRoe]

Thank you so much.

[rsw686]

Your welcome. You might need to change the

AND subject LIKE '%[review]%'

to include a f or l as I'm betting SQL will complain of multiple coulmns

AND f.subject LIKE '%[review]%'