CODEWITHSUNDEEP
sqlpostgresql
mahan
mahan

Reputation: 14975

postgresql/sql - join(Double?) two tables and sort by a column of the second table

I have two tables: assignment and message.

assignment

id message assignor
11 33 909
32 13 5464
52 521 909

message

id text state
33 Merheba NEW
43 Salam READ
312 Olá READ
521 hello NEW
412 Hola NEW
212 Hallo READ
765 Saluton READ

assignment refers to message.

I want to query all messages where their state is 'NEW' and their assignor is 909 in assignment or they do not exist in assignment. And I need to sort the list by assignor.

I have used this query to fetch all the messages but I am not sure if is possible to sort the result.

select *
from message
where (
  id not in (
    select message
    from assignment
  )
  or id in (
    select message
    from assignment
    where assignee = 909
  )
)
and state = 'NEW';

Is it possible to simplify the query? I look for the message two times.

The result must be:

id text state
33 Merhaba NEW
521 hello NEW
412 Hola NEW

The first two rows have assignor (909 in assignment), and all of the three rows are 'NEW'.

Upvotes: 0

Views: 63

Answers (2)

Alessio Cantarella
Alessio Cantarella

Reputation: 5201

Your schema:

CREATE TABLE assignment(
  id INT,
  message INT,
  assignor INT
);
CREATE TABLE message(
  id INT,
  text VARCHAR(255),
  state VARCHAR(255)
);

Your test data:

INSERT INTO assignment (id, message, assignor) VALUES
  (11, 33, 909),
  (32, 13, 5464),
  (52, 521, 909);
INSERT INTO message (id, text, state) VALUES
  (33, 'Merheba', 'NEW'),
  (43, 'Salam', 'READ'),
  (312, 'Olá', 'READ'),
  (521, 'hello', 'NEW'),
  (412, 'Hola', 'NEW'),
  (212, 'Hallo', 'READ'),
  (765, 'Saluton', 'READ');

Your requirements:

I want to query all messages where their state is 'NEW' and their assignor is 909 in assignment or they do not exist in assignment. And I need to sort the list by assignor.

The query you need:

SELECT message.*
FROM message
LEFT JOIN assignment
  ON message.id = assignment.message
WHERE message.state = 'NEW'
  AND (
    assignment.assignor = 909
    OR assignment.assignor IS NULL
  )
ORDER BY assignment.assignor;

Its result:

id text state
33 Merheba NEW
521 hello NEW
412 Hola NEW

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Your query is fine. I am inclined to write such logic using exists:

select m.*
from message m
where m.state = 'NEW' and
      (exists (select 1 from assignment a where a.message = m.id and a.assignee = 909) or
       not exists (select 1 from assignment where a.message = m.id)
      );

If messages do not have multiple assignees, then you can change this to:

select m.*
from message m
where m.state = 'NEW' and
      not exists (select 1
                  from assignment a
                  where a.message = m.id and a.assignee <> 909
                 );

EDIT:

In order to sort by assignor, you need to join that table in:

select m.*
from message m left join
     assignment a
     on m.id = a.message
where m.state = 'NEW' and
      not exists (select 1
                  from assignment a
                  where a.message = m.id and a.assignee <> 909
                 )
order by a.assignor;

And similarly for the second version.

You can also make the whole thing a join:

select m.*
from message m left join
     assignment a
     on m.id = a.message
where m.state = 'NEW' and
      (a.message is null or  -- no matches
       a.assignee = 909
      )
order by a.assignor;

Upvotes: 1

Related Questions