I have the following tables
forum_frm (ALIAS = F)
##########################
# id_frn # title_frm # desc_frm #
##########################
# 01 # NEWS # blahblahblah #
# 02 # HELP # blahblahblah #
# 03 # FAQS # blahblahblah #
##########################
topics_top (ALIAS = T)
#######################
# id_top # idfrm_top # title_top #
#######################
# 01 # 01 # new rel.. #
# 02 # 01 # update.. #
# 03 # 02 # stuck ... #
# 04 # 03 # install... #
# 05 # 02 # missing.. #
# 06 # 02 # cant se.. #
# 07 # 03 # removi... #
#######################
messages_msg (ALIAS = M)
###########################################
# id_msg # idfrm_msg # idtop_msg # date_msg # idusr_msg #
###########################################
# 01 # 01 # 01 # 02-03-2005 # 01 #
# 02 # 01 # 02 # 03-04-2005 # 01 #
# 03 # 01 # 01 # 15-04-2005 # 02 #
# 04 # 02 # 03 # 16-04-2005 # 03 #
continued.....
user_usr (ALIAS = U)
###############
# id_usr # name_usr #
###############
# 01 # Peter #
# 02 # Joe #
# 03 # Kate #
continued.....
From these tables I require a query to get a table that resembles this....
################################################## #######################################
# F.title_frm # F.desc_frm # topics count # post count # date of last post # last post title # last post by #
################################################## #######################################
With this query :
SELECT F.title_frm, F.desc_frm,This returns everything correctly apart from the "last post by" and "last post title". Mysql favouring the entries for the first post of each forum.
count(DISTINCT T.id_top) AS topiccnt,
count(DISTINCT M.id_msg) AS postcnt,
max(M.date_msg) AS lastpost,
T.title_top, U.name_usr
FROM
(((forum_frm F LEFT JOIN topic_top T ON T.idfrm_top=F.id_frm)
LEFT JOIN message_msg M ON M.idtop_msg=T.id_top)
LEFT JOIN user_usr U ON U.id_usr=M.idusr_msg)
GROUP BY F.index_frm
I hope someone can understand my explanation as it was a headache just writing it.
Anyway any help would be greatly appreciated
Thanks