CODEWITHSUNDEEP
sqlpostgresql
Arya
Arya

Reputation: 8985

complex sql query combining 'select max' and select count(*) queries

I have the following table, which is used for an auto-responder, it tracks all the messages exchanged to each person. It tracks each person by match_id

CREATE TABLE public.sms_log
(
  id bigint NOT NULL DEFAULT nextval('sms_log_id_seq'::regclass),
  source text NOT NULL,
  destination text NOT NULL,
  message text,
  insert_time timestamp with time zone DEFAULT now(),
  reply_batch boolean DEFAULT false,
  own_reply boolean DEFAULT false,
  match_id text NOT NULL,
  CONSTRAINT sms_log_pkey PRIMARY KEY (id),
  CONSTRAINT sms_log_match_id_fkey FOREIGN KEY (match_id)
      REFERENCES public.match (match_id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)

Right now I have the following query which returns rows of match_id if the last message sent had own_reply as false. (pretty much checks if someone sent the system a message from the last time the system replied to them)

QUERY A

select m.* 
from sms_log m 
where m.source <> 'MYNUMBER' 
  and m.destination = 'MYNUMBER' 
  and m.insert_time = (select max(insert_time) 
                       from sms_log 
                       where match_id = m.match_id 
                       group by match_id)

I then use a loop inside a program to determine how many times the program has replied to a match_id by using the following query

QUERY B

select count(*) from sms_log where match_id = ? and reply_batch = true

Is it possible to combine these two queries in a way that QUERY A would only return match_ids only if the reply_batch count is less than 3?

Upvotes: 0

Views: 48

Answers (1)

S&#233;bastien
S&#233;bastien

Reputation: 44

You could try this :

select m.* 
from sms_log m 
where m.source <> 'MYNUMBER' 
  and m.destination = 'MYNUMBER' 
  and m.insert_time = 
       (select max(insert_time) 
        from sms_log 
        where match_id = m.match_id group by match_id)
  and (select count(*) 
       from sms_log as sl 
       where sl.match_id = m.match_id 
         and sl.reply_batch = true) < 3

Upvotes: 1

Related Questions