Need Help Writing SQL Query in Postgresql

Question

I've been trying to write this query but can't seem to get it right for some reason or another..

What I need to do is:

Change the status of a question to 'closed' if there has not been an update associated with this question inserted into the qUpdateTable in the last 24 hours.

I only want it to be closed if a staff member has replied to it at least once.

You can determine if a staff member or a user has replied to the question by checking the qUpdateTable and seeing if a the StaffID field is empty or has a value for that particular tickets updates. If there is a staffID then it has been updated by a staff member, however if it does not then the qUpdate was done by a user.

Essentialy the way this works is a user posts a question by inserting into the Question table, and replies are made by inserting into the qUpdate table and linked to the original question using the foreign key - "QuestionID".

The tables:

CREATE TABLE Staff
(
    ID      INTEGER NOT NULL PRIMARY KEY,
    Name        VARCHAR(40) NOT NULL
);

CREATE TABLE Customer
(
    ID      INTEGER NOT NULL PRIMARY KEY,
    Name        VARCHAR(40) NOT NULL,
    Email       VARCHAR(40) NOT NULL
);

CREATE TABLE Product
(
    ID      INTEGER NOT NULL PRIMARY KEY,
    Name    TEXT NOT NULL
);

CREATE TABLE Question
(
    ID      INTEGER NOT NULL PRIMARY KEY,
    Problem VARCHAR(1000),
    Status  VARCHAR(20) NOT NULL DEFAULT 'open', 
    Priority    INTEGER NOT NULL,
    LoggedTime  TIMESTAMP NOT NULL,
    CustomerID  INTEGER NOT NULL,
    ProductID   INTEGER NOT NULL,
    FOREIGN KEY (ProductID) REFERENCES Product(ID),
    FOREIGN KEY (CustomerID) REFERENCES Customer(ID),
    CHECK (Status IN ('open','closed') AND Priority IN (1,2,3))

);

CREATE TABLE qUpdate
(
    ID      INTEGER NOT NULL PRIMARY KEY,
    Message VARCHAR(1000) NOT NULL,
    UpdateTime  TIMESTAMP NOT NULL,
    QuestionID  INTEGER NOT NULL,
    StaffID INTEGER,
    FOREIGN KEY (StaffID) REFERENCES Staff(ID),
    FOREIGN KEY (QuestionID) REFERENCES Question(ID)
);

Some sample inserts:

INSERT INTO Customer (ID, Name, Email) VALUES (1, 'testname1', 'testemail1');

INSERT INTO Customer (ID, Name, Email) VALUES (2, 'testname2', 'testemail2');

INSERT INTO Staff (ID, Name) VALUES (1, 'Don Keigh');

INSERT INTO Product (ID, Name) VALUES (1, 'Xbox');

INSERT INTO Question (ID, Problem, Status, Priority, LoggedTime, CustomerID, ProductID) 
VALUES  (1, 'testproblem1', 'open', 3, '2012-04-14 09:30', 2, 1);

INSERT INTO Question (ID, Problem, Status, Priority, LoggedTime, CustomerID, ProductID) 
VALUES  (2, 'testproblem2', 'open', 3, '2012-04-14 09:30', 2, 1);

INSERT INTO qUpdate (ID, Message, UpdateTime, StaffID, QuestionID) VALUES (2, 'testmessage1','2012-07-12 14:27', 1, 1);

INSERT INTO qUpdate (ID, Message, UpdateTime, QuestionID) VALUES (3, 'testmessage1','2012-06-18 19:42', 2);

What I've done so far (which obviously doesn't work)

UPDATE Question
SET Status = 'closed' 
WHERE EXISTS
(SELECT qUpdate.QuestionID  
MAX(qUpdate.UpdateTime - Now() = INTERVAL '1 day') FROM qUpdate
LEFT JOIN Question ON qUpdate.QuestionID = Question.ID
WHERE qUpdate.StaffID IS NOT NULL);

I realise my explanation may be a bit confusing so if you need more info post and I'll reply ASAP

Answer 1

UPDATE Question
SET Status = 'closed' 
where
-- this clause asserts there's at least one staff answer
exists (
  select null from qUpdate
  where qUpdate.QuestionID = Question.ID
  and StaffID is not null
)
-- this clause asserts there's been no update in the last 24 hours
and not exists (
  select null from qUpdate
  where qUpdate.QuestionID = Question.ID
  and qUpdate.UpdateTime > (now() - interval '24 hours') 
)
and Status = 'open';

You'll almost certainly want an index on qUpdate(QuestionID) or possibly qUpdate(QuestionID,UpdateTime) or qUpdate(QuestionID,StaffID) to get good performance on the subselects in the exists() tests.