Postgres fetch exact value when satisfied all conditions

All we need is an easy explanation of the problem, so here it is.

Here are the table structure and sample data.

CREATE TABLE public.product (
                id serial NOT NULL,
                opid int4 NULL,
                opvalue int4 NULL,
                info varchar NULL,
                CONSTRAINT product_pkey PRIMARY KEY (id)
            );

INSERT INTO product (id,opid,opvalue,info) VALUES 
            (1,1,1,'s1')
            ,(2,1,1,'s2')
            ,(3,2,1,'s2')
            ,(4,1,1,'s3')
            ,(6,3,2,'s3')
            ,(5,2,1,'s3')
            ;

enter image description here

Now I want query if info value satisfied all it’s condition value then it will be returned.
Ex.If I pass opid=1 and opvalue=1 then only s1 should be returned not s2 because s2 have it’s different condition. s1 should be returned when I pass (opid=1 and opvalue =1) and (opid=2 and opvalue =1)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I think this does what you want:

select *
from product p
where opid = 1 and opvalue = 1
and not exists (
  select *
  from product x
  where x.info = p.info
  and (x.opid != p.opid or x.opvalue != p.opvalue)
);

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=1efcb7a125d52c27b2c386307d37da5b

Method 2

WITH cte AS 
(
SELECT *,
       COUNT(info) OVER (PARTITION BY info) total_count,
       SUM(CASE WHEN opid=1 AND opvalue=1 THEN 1 END) OVER (PARTITION BY info) conditional_count
FROM product
)
SELECT id, opid, opvalue, info
FROM cte
WHERE total_count = conditional_count;

fiddle

PS. If there exists more than one record which matches your conditions (full duplicate except id field) then remove id from output fields list and add DISTINCT.

Method 3

Question is solved via creating one summary table of this table and manage count of info in parent table then using query on count of that parent table.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply