Need to create a report from 2 Tables but getting Error messages
I need to create a report looking at 2 different Tables.
Lets say table 1 is called "support_ticket", this table contains the main info about a new support ticket, such as:
Author, Date_Posted, Author_ID, Address , etc.
Table 2 contains the messages posted and replies posted to the messages, it is called table "replies" and it contains info such as:
Reply_Author, Reply_Message, Reply_date, Reply_to_what
So here is what I need to do:
create a report which lists those new Tickets that have been opened which have not been answered?
I tried this but it is not working:
SELECT id, LEFT(subject, 40) AS mini_subject , author, status, date_added FROM support_ticket, replies
WHERE support_ticket.id = replies.support_id
AND MAX(reply_date) IS NULL
GROUP BY id;
Your help is greatly appreciated.