Question 89679 in PostgreSQL Question posted in
The official documentation can be found here.

Check if a value is found in all groups of another table – Postgresql

Postgresql ver 15

Let’s say I have a list of values, e.g.

ID
alpha
beta
delta
.
.
.

and I wish to check for each value, whether such a value exists in every group that is partitioned in a table, e.g.

GROUP ID
A     alpha
A     aplha
B     alpha
B     peanuts
...

In the above table, let’s assume the id alpha is in every group, so that check is true.

How do I do this check? Appreciate any advice given on how to start.

Tags:

2

Answers


  1. 0

    You can use PostgreSQL Except function to do that.

    First, find out how many group we have,

    Then, find out a list of ID exists in every group,

    Lastly, compare it with the master ID list using Except function.
    If there is any ID shown in the result, those are the ones NOT in every group.

    See the following code as example

    WITH total_group_number AS (
SELECT
    COUNT(DISTINCT group_name) as total_group_count
FROM
    group_list
),
id_group_number AS (
SELECT
   ID,
   COUNT(DISTINCT group_name) AS group_count
FROM
   group_list
GROUP BY
   ID
)
SELECT 
    DISTINCT ID 
FROM 
    ID_LIST
EXCEPT
SELECT
    ID
FROM
    id_group_number
CROSS JOIN
    total_group_number
WHERE
    id_group_number.group_count = total_group_number.total_group_count

    another way to use window function in PostgreSQL

    See the following code as example

    WITH group_number AS (
SELECT
    ID,
    COUNT (DISTINCT group_name) OVER() as total_group_count,
    COUNT (DISTINCT group_name) OVER(PARTITION BY ID) as id_group_count
FROM
    group_list
)
SELECT 
    DISTINCT ID 
FROM 
    ID_LIST
EXCEPT
SELECT
    DISTINCT ID
FROM
    group_number
WHERE
    id_group_count = total_group_count
    Login or Signup to reply.
  2. 0

    First cte is to get Distinct Groups and total number of groups, then on the second cte we apply cross join to the ID that we are looking for, then an inner join to check if the total number match with count of the rows that satisfies both the table :

    with cte as (
  select GROUPE, count(1) over() as group_number
  from mytable
  group by GROUPE
),
cte2 as (
  select *
  from cte
  cross join (select 'alpha' as ID) as s
),
cte3 as (
  select DISTINCT *
  from cte2 c2
  inner join mytable t on t.GROUPE = c2.GROUPE and t.ID = c2.ID
)
select case when count(1) = group_number then 'YES' else 'NO' end as check
from cte3
group by group_number;

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search