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

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.

CodePudding user response：

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.