CODEWITHSUNDEEP
sqlpostgresql
Arya
Arya

Reputation: 8995

Using not like in a group by

I have the following table which keeps tracks of chat messages

match_id is pretty much IDs that identify a chat conversation.

CREATE TABLE message_log
(
  id integer,
  message text,
  from_id character varying(500),
  to_id character varying(500),
  match_id character varying(500),
  own_account boolean,
  reply_batch boolean DEFAULT false,
  insert_time timestamp with time zone DEFAULT now(),
)

I have constructed the following query

select match_id from message_log where message not like '%@%' and own_account = true group by match_id

Which should return all the match_ids if no message with the same match_id is sent that contains "@". But The query still returns some rows that contain messages which has "@" in it.

How can I modify the query that it would only return match_ids if no message contains "@" related to the match_id

Upvotes: 0

Views: 35

Answers (2)

Naruto
Naruto

Reputation: 4329

First of all your query is not correct, there is an extra comma at the end.

CREATE TABLE message_log
(
  id integer,
  message text,
  from_id character varying(500),
  to_id character varying(500),
  match_id character varying(500),
  own_account boolean,
  reply_batch boolean DEFAULT false,
  insert_time timestamp with time zone DEFAULT now()
)

Secondly,

select distinct(match_id) from message_log where own_account = TRUE and match_id not in (select `match_id` from message_log where message like '%@%')

Upvotes: 1

wvdz
wvdz

Reputation: 16651

You want to exclude all match_id for which there exists a message that contains '@'.

You can solve it with a subquery and a not exists construct.

SELECT ml.match_id
FROM message_log ml
WHERE NOT EXISTS (
     SELECT 1 FROM message_log
     WHERE message LIKE '%@%'
     AND match_id = ml.match_id)
  AND ml.own_account = TRUE
GROUP BY ml.match_id

Upvotes: 1

Related Questions