Postgresql select count even if the joining table has empty record

Question

I have a user table as below

table.users

{id:1, name:'USER 1'}
{id:2, name:'USER 2'}
{id:3, name:'USER 3'}
{id:4, name:'USER 4'}

The USER 1 sends message to all users with a chat window id is inserted in table.messages

table.messages

{id:1,chat_id:1, from_user:1: message:'Hi'}
{id:2,chat_id:1, from_user:1: message:'How are you'}
{id:3,chat_id:1, from_user:1: message:'This is broadcasted'}

table.last_read

{id:1, last_read_message_id:3, user_id:2, chat_id:1}
{id:2, last_read_message_id:3, user_id:3, chat_id:1}

I am using the below query to fetch the unread message by the user , as the table.last_read inserting only when alteast one read ,USER 4 will not have record in table.last_read, the below returns last_read_message_id null in the left join and returns 0 records

   SELECT COUNT(*) FROM messages  m
    LEFT JOIN (
        SELECT last_read_message_id
        FROM last_read  
        WHERE user_id=4
    ) lr ON m.chat_id=lr.chat_id
    WHERE m.chat_id=1 AND m.id>last_read_message_id

Nick · Accepted Answer · 2019-03-05 06:56:46Z

1

The use of the LEFT JOIN is negated because you are referencing a field from that table in your WHERE clause. It is behaving like an INNER JOIN as a result.

To fix this, add the criterion to your JOIN specification instead.

SELECT COUNT(*)
FROM messages m
LEFT JOIN last_read lr
  ON  m.chat_id = lr.chat_id
  AND m.id > lr.last_read_message_id
  AND lr.user_id = 4
WHERE m.chat_id = 1

This is the equivalent of yours with the last_read_message_id moved from the WHERE to the JOIN.

Fiddle: http://sqlfiddle.com/#!17/c0b0f/2

edited Mar 5, 2019 at 6:56

answered Mar 5, 2019 at 6:30

Nick

7,5012 gold badges25 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Niyaz Over a year ago

SELECT COUNT(*) FROM messages m LEFT JOIN last_read lr ON m.chat_id = lr.chat_id AND m.id > lr.last_read_message_id AND lr.user_id = 2 WHERE m.chat_id = 1 will return 3 rows , in that case the messages are already read and should return 0 as count as the USER 2 read all the messages

Nick Over a year ago

Is this now a different question than what you originally asked?

Collectives™ on Stack Overflow

Postgresql select count even if the joining table has empty record

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related