Search code examples
sqlderby

SQL - Is COUNT() possible in this situation?

I am working on a project that requires me to build a forum system from sketch

However I am facing a problem regarding SQL

I am using Derby Database.

Table Structure as follow.

 

TABLE ForumThread (
    TID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 , INCREMENT BY 1) Primary Key,
    TTitle varchar(50) NOT NULL,
    UID Integer NOT NULL,
    CID Char(7) NOT NULL,
    Sticky Boolean Not null,
    Status char(1)
)

==========Content and reply of a thread========
TABLE ForumThreadContent (
    RID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 , INCREMENT BY 1) Primary Key,
    Rcontent varchar(10000) Not Null,
    RTime Timestamp Not Null,
    REditTime Timestamp,
    UID Integer NOT NULL,
    TID Integer NOT NULL,
    Status Char(1) 
)


TABLE Users (
   UID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 , INCREMENT BY 1) Primary Key,
   UName varchar(50) NOT NULL,
   UNick varchar(50) Not Null,
   ULoginName varchar(16) NOT NULL,
   Upwd varchar(16) NOT NULL,
   UPic varchar(200),
   UType Char(1) NOT NULL

)

=========Foreign Key==========
ALTER TABLE ForumThreadContent
ADD CONSTRAINT Forum_reply_fk_thread Foreign KEY (TID) REFERENCES ForumThread(TID);
Alter Table ForumThread
Add Constraint forum_thread_fk_User Foreign Key (UID) References Users(UID);

The UI Design of displaying a single thread in forum will be displayed the following information. With 10 threads to be displayed each page

  • Thread Title (ForumThread.TTitle)
  • Thread Author (Users.UNick)
  • Number of replies in that thread
  • Latest reply time (ForumThreadContent RTime)
  • Latest replier's nickname (Users.UNick)

My SQL Statement.

SELECT ftc.tid ,ft.TTitle, ss.uNick as "Author", s.Unick as "Last replied by" , MAX(ftc.RTIME) as "Last Reply Time", COUNT(*) AS "Posts Count"
FROm ForumThreadContent ftc, Users s, ForumThread ft, Users ss
WHERE ftc.UID = s.UID
      AND ftc.TID = ft.TID 
      AND ft.UID = ss.UID
Group by ftc.tid , ft.ttitle , s.uNick , ss.uNick
Having MAX(ftc.RTIME) IN (SELECT MAX(ftcc1.RTime) 
                      FROM ForumThreadContent ftcc1 
                      WHERE ftc.TID = ftcc1.tid)

This statement can be executed without error. Assuming there are 3 replies in TID "1" , when I run it, the COUNT(*) only counted "2" for tid: 1. To be clear:

  • 3 rows in ForumThreadContent have a foreign key "1" of TID
  • first rows of 3 rows has a foreign key "1" of UID
  • The rest has a foreign key "2" of UID

Is it possible to do a count() function correctly in this situation? I can never get a number higher than 2 with my SQL.

Solution

  • This might work for you. Please check for column names once

    SELECT t1.tid, t1.Title, t1.Author, s1.Unick as 'Last replied by', ftc1.Rtime as 'last reply time', t1.count
FROM
(SELECT ftc.tid, ft.Title, s.UID, s.Unick as 'Author', COUNT(*) as count FROM 
ForumThreadContent ftc, Users s, ForumThread ft
where ftc.TID = ft.TID
and ft.UID = s.UID
group by ftc.tid, ft.title, s.UID) t1, Users s1, ForumThreadContent ftc1
where ftc1.rtime = (select max(rtime) from ForumThreadContent ftc2 where ftc2.tid = t1.tid)
and ftc1.uid = s2.uid