CODEWITHSUNDEEP
sqlpostgresqlfilter
Ya Bü
Ya Bü

Reputation: 21

Postgresql Return multiple rows, if one row satisfies a condition

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.

Upvotes: 1

Views: 1589

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

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;

Upvotes: 3

Related Questions