Postgresql Return multiple rows, if one row satisfies a condition

Question

I have the following table:

Tablename: yay

id | tagname |  value  
----+---------+---------
0  | Farbe   | Gelb
0  | Preis   | 1.15
0  | Thema   | Herbst
1  | Farbe   | Maigrün
1  | Preis   | 1.15
1  | Thema   | Herbst
2  | Farbe   | Schwarz
2  | Preis   | 1.15
2  | Thema   | Sommer

What i want is to get all rows of an id for which one or more conditions are satisfied and one or more where not. If, for example, i want all id with their rows in the table where tagname='Preis', value='1.15' and tagname=Thema, value='Herbst' is satisfied, but don't want id where tagname='Farbe', value='Schwarz' becomes true. The result should look like this:

id | tagname |  value  
----+---------+---------
0  | Farbe   | Gelb
0  | Preis   | 1.15
0  | Thema   | Herbst
1  | Farbe   | Maigrün
1  | Preis   | 1.15
1  | Thema   | Herbst

When at least one condition for inclusion is met, all rows containing the id where the condition was met are to be in the result. But if at least one condition for the exclusion is met, then no row of the corresponding id shall be in the result.

Gordon Linoff · Accepted Answer · 2014-09-30 17:28:34Z

3

If you just want the ids, you can do:

select id
from yay
group by id
having sum(case when tagname = 'preis' and value = '1.15' then 1 else 0 end) > 0 and
       sum(case when tagname = 'Thema' and value = 'Herbst' then 1 else 0 end) > 0 and
       sum(case when tagname = 'Farbe' and value = 'Schwarz' then 1 else 0 end) = 0;

Each condition counts the number of matching rows. The first two require at least one match (each) for an id, because of the > 0. The third says there are no match, because of the = 0.

You can get the original data by joining back:

select yay.*
from yay join
     (select id
      from yay
      group by id
      having sum(case when tagname = 'preis' and value = '1.15' then 1 else 0 end) > 0 and
             sum(case when tagname = 'Thema' and value = 'Herbst' then 1 else 0 end) > 0 and
             sum(case when tagname = 'Farbe' and value = 'Schwarz' then 1 else 0 end) = 0
     ) yid
     on yay.id = yid.id;

answered Sep 30, 2014 at 17:28

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ya Bü Over a year ago

That's what i've been searching for. Thank you!

Collectives™ on Stack Overflow

Postgresql Return multiple rows, if one row satisfies a condition

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related