Seemingly simple mysql query

Databases
1

Hi All,

I’ve been programming with php for quite a long time, but have always got by using pretty rubbish database handling - and so i’m trying to improve my use of the database by minimising connections and using more complex queries where in the past I would flipently use multiple queries just to get something to work.

I have 2 tables - forum_thread and forum_thread_reply. forum_thread has the primary key ‘foth_id’ which is a foreign key in forum_thread_reply.

I want a query that lists all forum_threads where the forum_id = (let’s say) 1 - as well as counting the number of records in forum_thread_reply where foth_id is the same as that of the forum_thread that is being selected.

Hopefully that makes some sort of sense. I reached this point before giving up:

SELECT
ft.foth_id,
ft.foru_id,
ft.foth_title,
ft.foth_body,
ft.foth_creator,
ft.foth_timestamp,
COUNT(ftr.fotr_id) as replies
FROM
forum_thread as ft,
forum_thread_reply ftr
WHERE
ft.foru_id = ‘1’
AND
ftr.foth_id = ft.foth_id

2

Thanks again - adapted this to do exactly what I wanted!

3

Again, thanks so much for the help - but what if I also wanted another column in the search - that of the highest fotr_timestamp (forum_thread_reply timestamp) - and then order the query by that?

4

Thank you very much! Didn’t realise it’d be that complicated. I feel I have a heck of a lot to learn about SQL!

5 
SELECT ft.foth_id
     , ft.foru_id
     , ft.foth_title
     , ft.foth_body
     , ft.foth_creator
     , ft.foth_timestamp
     , COALESCE(r.replies,0) AS replies
  FROM forum_thread as ft
LEFT OUTER
  JOIN ( SELECT foth_id
              , COUNT(*) AS replies
           FROM forum_thread_reply 
         GROUP
             BY foth_id ) AS r
    ON r.foth_id = ft.foth_id
 WHERE ft.foru_id = 1
6 
SELECT ft.foth_id
     , ft.foru_id
     , ft.foth_title
     , ft.foth_body
     , ft.foth_creator
     , ft.foth_timestamp
     , COALESCE(r.replies,0) AS replies
     , [COLOR="Blue"]COALESCE(r.last_reply,0) AS last_reply[/COLOR]
  FROM forum_thread as ft
LEFT OUTER
  JOIN ( SELECT foth_id
              , COUNT(*) AS replies
              , [COLOR="blue"]MAX(fotr_timestamp) AS last_reply[/COLOR]
           FROM forum_thread_reply 
         GROUP
             BY foth_id ) AS r
    ON r.foth_id = ft.foth_id
 WHERE ft.foru_id = 1
[COLOR="Blue"]ORDER
    BY last_reply DESC[/COLOR]