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

Question

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'.

Gordon Linoff · Accepted Answer · 2019-07-10 11:47:20Z

1

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;

edited Jul 10, 2019 at 11:47

answered Jul 10, 2019 at 11:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

mahan Over a year ago

Thanks. Messages have unique assignee. I liked the solution, but it does not sort.

Alessio Cantarella · Accepted Answer · 2021-02-06 21:05:38Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related