0

I'm working on a similar problem to check if a column contains ALL the values of another column - Mysql

This CTE is part of a bigger query. CTE_ProjekteRollen contains a subset of ProjektParamRolle and can contain zero or more records. I want a list of ProjektParam where the items in CTE_ProjekteRollen are all present (when joined with ProjektParamRolle). My solution works in all cases where CTE_ProjektRollen is not empty.

CTE_FilteredByRolle as (
    select pp.ID_ProjektParam
    from Basis.ProjektParam pp
    join Basis.ProjektParamRolle ppr
        on pp.ID_ProjektParam = ppr.ID_ProjektParam
    join CTE_ProjektRollen pr
        on ppr.Rolle = pr.Rolle
    group by pp.ID_ProjektParam
    having Count(pp.ID_ProjektParam) = (
        select Count(Rolle)
        from CTE_ProjektRollen))

What do I have to change to get all ProjektParam (joined with ProjektParamRolle), if CTE_ProjektRollen is empty?

Edit: I think I phrased my question wrong, because I didn't understand it fully. @Kendle's solution works for what I described, but I actually needed all ID_ProjektParam (not joined with ProjektParamRolle).

The actual CTE that worked for me was

CTE_FilteredByRolle as (
    select pp.ID_ProjektParam
    from Basis.ProjektParam pp
    where (
        select Count(ppr.Rolle)
        from Basis.ProjektParamRolle ppr
        join CTE_ProjektRollen pr
            on ppr.Rolle = pr.Rolle
        where ppr.ID_ProjektParam = pp.ID_ProjektParam) = (
            select Count(Rolle)
            from CTE_ProjektRollen))
G_hi3
  • 588
  • 5
  • 22
  • Are you sure about the condition `having Count(distinct pp.ID_ProjektParam) = ...`? You `group by pp.ID_ProjektParam` so `Count(distinct pp.ID_ProjektParam)` will always return `1` (or `0` if `pp.ID_ProjektParam` is nullable). – forpas May 02 '22 at 16:21
  • @forpas you're right, I had it working without the distinct. I edited the question accordingly. – G_hi3 May 03 '22 at 06:27

1 Answers1

1

We can use a CASE to check whether the table is empty. If it is empty we return the number to which we are comparing, so it will always be true.

CTE_FilteredByRolle as (
    select pp.ID_ProjektParam
    from Basis.ProjektParam pp
    join Basis.ProjektParamRolle ppr
        on pp.ID_ProjektParam = ppr.ID_ProjektParam
    join CTE_ProjektRollen pr
        on ppr.Rolle = pr.Rolle
    group by pp.ID_ProjektParam
    having Count(distinct pp.ID_ProjektParam) 
        = case when(select Count(distinct Rolle) from CTE_ProjektRollen)) = 0 
               then Count(distinct pp.ID_ProjektParam) 
               else (select Count(distinct Rolle) from CTE_ProjektRollen))
               end;