Quote
select
person_id, name, vorname, email
sum( if(detail_id=6 and status=1,1,0) ) as d1s1,
sum( if(detail_id=3 and status=0,1,0) ) as d2s0,
sum( if(detail_id=3,1,0) ) as d4
from detailzuordnung
LEFT JOIN person ON (detailzuordnung.person_id = person.id)
LEFT JOIN adressen ON (adressen.Person_id = adressen.Person_id)
group by person_id
having (d1s1>0 and d2s0>0) OR (d1s1>0 and d4=0)
Many problems with this query ...
1 It doesn't parse ... missing comma after email
2 As MySQL tells you, it can't read the query writer's mind, so every column name in the Select list that occurs in more than one table in the From clause needs to qualified by its table name or (preferably for reliability and readability) alias
3 Joins to tables that `detailzuornumg` columns refer to shouldn't be Left Joins, that makes no sense, they need to be Inner Joins
4 The Join On clause (adressen.Person_id = adressen.Person_id) is just a tautology, it'll always return true
5 In a Group By query, non-aggregating columns not in the Group By clause columns return arbitrary values unless they're 100% determined by the Group By columns, so merely adding columns and Join clauses to a working Group By query like this is not the right approach, rather detail from other tables needs to be assembled in an outer query
... so I think you something more like this ...
select p.person_id, p.name, p.vorname, a.email
from (
select person_id,
sum( if(detail_id=6 and status=1,1,0) ) as d1s1,
sum( if(detail_id=3 and status=0,1,0) ) as d2s0,
sum( if(detail_id=3,1,0) ) as d4
from detailzuordnung
group by person_id
having (d1s1>0 and d2s0>0) or (d1s1>0 and d4=0)
) as x
join person as p on (d.person_id = x.person.id);
join adressen as a on (a.person_id = p.person_id);
... though I can't vouch for the accuracy of my guess about what table has the email column since I don't have the `adressen` table and your DDL script threw too many errors.
Edited 2 time(s). Last edit at 05/20/2020 12:36PM by Peter Brawley.