Home PostgreSQL select values from one column that satisfy conditions in two other columns

Questions

PostgreSQL select values from one column that satisfy conditions in two other columns

December 9, 2022

I have the following table:

uid source  source_id
1   a   101
1   b   201
1   c   301
2   a   102
2   c   302
3   a   103
3   b   203

and want to create a view from a query that returns the source_id for rows where source = ‘b’, where both a and b are present for any common uid value.

uid source_id
1   201
3   203

I have tried several queries involving aggregation, HAVING CASE, WHERE EXISTS etc., but nothing comes close to working so far.

>Solution :

We can use an aggregation approach as follows:

SELECT
    uid,
    MAX(source_id) FILTER (WHERE source = 'b') AS source_id
FROM yourTable
WHERE source IN ('a', 'b')
GROUP BY uid
HAVING COUNT(DISTINCT source) = 2;

Here we aggregate by the uid and assert that a match have both source a and b. We use pivoting logic to report the source_id for source b.