I have the following columns in my table (I’m using postgresql): parent, child, status. Each parent has 1 or 2 children.
I want to select all parents whose first child has status x and second child has status y or in case there’s only 1 child than parent with child with status x. For example, a table with the following rows:
| parent | child | status |
|---|---|---|
| a | child1 | x |
| a | child2 | y |
| b | child3 | x |
should return parents a and b.
I started building the query something like:
select parent from "mytable" group by parent having ...
but I don’t know how to add the above condition for status within having clause.
Advertisement
Answer
SQL tables represent unordered sets. You need a column that specified what “first” and “second” means. Let me assume that is the child column.
Then you can use:
select parent
from mytable
group by parent
having string_agg(status order by child) in ('x', 'xy');