How to compare the result of two querys, or using one as a subquery?-CodePudding

I have a query that checks for the first registry that meet the conditions, the most important one being OK inside one of the columns with a json.

where p.metadata @> '{"state":"OK"}'
and 1=1
and ip.id = 62
and l.insurance_payment_id = 62
and (p.created_at::timestamptz at time zone 'America/Santiago' BETWEEN '2022-06-22 00:00:00-04'::timestamptz AND '2022-06-22 23:59:59-04'::timestamptz)
and op.id = 9
order by ps asc, p.created_at asc
limit 1

Now if a register comes in, I need to check for a foreign key id and see if it has enter for the first time or not, using this query as a sub query to compare the results, how can I do that? I'm using postgresql.

EDIT: I forgot to add this:

where
  1=1
  AND ip.id=62
  AND l.insurance_payment_id=62
  AND (p.created_at::timestamptz at time zone 'America/Santiago' BETWEEN '2022-06-22 00:00:00-04'::timestamptz AND '2022-06-22 23:59:59-04'::timestamptz 
        AND op.id = 9) 
  /*AND no previous registries with state OK before*/
  and ps.metadata->>'state' = 'OK') as foo

This is where I shoud add the query, inside the commented line. I have the query that searches for the first registry ok to meet those conditions if there is one.

CodePudding user response：

Assuming you can boil down the condition of no previous entries with state OK into a query, you can limit your first query with the EXISTS clause.

Assume your condition is:

SELECT id
FROM other_tables
WHERE state='OK'

Then you can modify your first query like so,

where
  1=1
  AND ip.id=62
  AND l.insurance_payment_id=62
  AND (p.created_at::timestamptz at time zone 'America/Santiago' BETWEEN '2022-06-22 00:00:00-04'::timestamptz AND '2022-06-22 23:59:59-04'::timestamptz 
        AND op.id = 9) 
  AND EXISTS
      (SELECT id
       FROM other_tables
       WHERE state='OK'
        and id = ip.id)

EXISTS acts sort of like a True/False, so you don't actually have to SELECT anything (notice I did SELECT 1) and the join to your data takes place inside the WHERE clause inside EXISTS.

CodePudding user response：

I managed this approach and it worked, may be good to share it

where
  1=1
  AND ip.id=62
  AND l.insurance_payment_id=62
  AND ((select p2.created_at from qppayment.payment p2 
        where p2.metadata @> '{"state":"OK"}'
        and p2.product_subscription_id = ps.id
        order by p2.created_at asc limit 1)::timestamptz at time zone 'America/Santiago'
    BETWEEN '2022-06-22 00:00:00-04'::timestamptz AND '2022-06-22 23:59:59-04'::timestamptz 
    and op.id = 9)
  and ps.metadata->>'state' = 'OK') as foo