SELECT
T.id,
T.display_date,
T.expire_date,
T.class,
T.title,
T.has_content,
T.visibled,
T.photo_count,
T.attach_count,
A2.display_name,
A2.link_name,
A2.extension,
A2.size
FROM (
SELECT
NP.id,
NP.display_date,
NP.expire_date,
NP.class,
NP.title,
NP.has_content,
NP.visibled,
NP.photo_count,
COUNT(A1.id) AS attach_count
FROM (
SELECT
N.id,
DATE_FORMAT(N.display_date, "%Y-%m-%d") AS display_date,
N.expire_date,
N.class,
N.title,
N.has_content,
N.visibled,
COUNT(P.id) AS photo_count
FROM
`topics_text` N
LEFT JOIN
`topics_photo` P
ON
P.id_text = N.id
AND
N.enabled = 1
GROUP BY
N.id,
N.display_date,
N.expire_date,
N.class,
N.title,
N.has_content,
N.visibled
HAVING
(N.display_date >= "2009-01-01")
AND
(N.display_date <= "2009-12-31")
AND (N.visibled = 1)
) NP
LEFT JOIN
`topics_attach` A1
ON
A1.id_text = NP.id
GROUP BY
NP.id,
NP.display_date,
NP.expire_date,
NP.class,
NP.title,
NP.has_content,
NP.visibled,
NP.photo_count
) T
LEFT JOIN
`topics_attach` A2
ON
A2.id_text = T.id
AND
T.attach_count = 1
ORDER BY
T.display_date DESC,
T.id DESC
