Greetings to all. The following table is available:

CREATE TYPE ref AS ENUM ('a', 'b', 'c', 'd', 'e'); CREATE TABLE table1(id SERIAL, param1 INTEGER, param2 ref, param3 VARCHAR(64), param4 VARCHAR(64)); CREATE UNIQUE INDEX SOMEINDEX ON table1 (param1,param2,param3);

Approximate values ​​for table1 :

 id |param1 | param2 | param3 | param 4 ====================================== 1 | 0 | a | sss | ttt 2 | 1 | a | sss | ttt 3 | 0 | b | nnn | hhh 4 | 2 | a | sss | ttt 5 | 1 | b | fff | fff 6 | 0 | c | ccc | mmm 7 | 0 | b | ttt | rrr 8 | 1 | b | nnn | hhh 9 | 2 | b | nnn | hhh 10 | 0 | a | ccc | zzz 11 | 1 | a | ccc | zzz 12 | 2 | a | ccc | zzz

param1 - can be any integer value

you must select param3 , param4 , which * are in all param1 records with previously known param2 (for example, where param2 = 'a' )

As a result, you need to get:

if WHERE param2 = 'a'

 param3 | param4 ================ sss | ttt ccc | zzz

if WHERE param2 = 'b'

 param3 | param4 =============== nnn | hhh

How to create a SQL query for such a choice?

    2 answers 2

    It?

     select param3,param4 from table1 where param2='a' group by param3,param4 having count(*) = (select count(distinct param1) from table1 where param2='a');

      HAVING can be used to compare the number of different param1 values:

       SELECT param2, param3, param4 FROM table1 WHERE param2 = 'b' GROUP BY param2, param3, param4 HAVING COUNT(DISTINCT param1) = ( SELECT COUNT(DISTINCT param1) FROM table1 WHERE param2 = 'b' )