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.
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
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]%'
Thank you so much.
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]%'