This is my table structure:

```
tblTickets
ticket_id | subject | creator_id | created_date | last_updated | category_id | ticket_status

tblMessages
msg_id | msg | ticket_id | replied_by | reply_time

tblReadBy   - PrimaryKey: (ticket_id,user_id)
ticket_id | user_id | last_read_time

```

Am trying to list all the tickets to the currently logged in User. That is, tickets that were created by them only.

Also, am showing a small color indication to let the the User know whether there's any unread messages.

So, whenever a User views a Ticket(by clicking from the list), a record will be either inserted/updated in `tblReadBy` with the `ticket_id` and `user_id` along with the current time)

My query for listing Tickets looks somewhat like this:

```
SELECT 
    t.`ticket_id`, 
    t.`subject`, 
    t.`user_id`, 
    t.`created_date`, 
    t.`last_updated`
    ( 
        SELECT COUNT(*) 
        FROM `tblReadBy` 
        WHERE `ticket_id` = t.`ticket_id` AND 
              `user_id` = 'XXX' AND 
              `last_read_time` < t.`last_updated` 
    ) AS `has_unread_msg`
FROM `tblTickets` AS t
WHERE t.`creator_id` = 'XXX'
ORDER BY t.`last_updated` DESC

```

Here, `XXX` is the user\_id of the currently logged in User. This query works fine if there's a row in `tblReadBy` table for the currently logged in `user_id` and the `ticket_id`. 

But if there's no row exists for this `user_id` and `ticket_id`, then as per logic it's an "unread ticket", but the query would return `0` for `has_unread_msg` column.

How can I fix this particular case?

**NOTE:** There would be more than one user replying to a Ticket. Like the actual User, Moderator (more than one), Admin, etc.

Thank you

---

**SOLUTION:**

So far, what I figured out is this:

```
SELECT 
    t.`ticket_id`, 
    t.`subject`, 
    t.`user_id`, 
    t.`created_date`, 
    t.`last_updated`
    ( 
        SELECT 
               COALESCE(SUM(CASE WHEN `last_read_time` < t.`last_updated` THEN 1 ELSE 0 END), 1)
        FROM `tblReadBy` 
        WHERE `ticket_id` = t.`ticket_id` AND 
              `user_id` = 'XXX'                  
    ) AS `has_unread_msg`
FROM `tblTickets` AS t
WHERE t.`creator_id` = 'XXX'
ORDER BY t.`last_updated` DESC

```

Am still trying to test all possible cases.