Multiple Havings not working

Question

I have a database with the following schema:

ID   PositionId    LeagueId
1        4            5
3        4            5
3        8            5
4        1            6

I have this sql query in Access:

 SELECT lp.PositionId
 FROM Leagues l 
 INNER JOIN Lineups lp ON (l.LeagueID = lp.LeagueId) 
 GROUP BY PositionId
 HAVING sum(iif(lp.PositionId = 4,1,0)) > 1 AND sum(iif(lp.PositionId = 8,1,0)) > 0

If I only use the left side of the Having, ie:

 HAVING sum(iif(lp.PositionId = 4,1,0)) > 1

I will get 1 results (LeagueId 5). If I use the right-side of the Having, ie:

 HAVING sum(iif(lp.PositionId = 8,1,0)) > 0

I will also get one result (LeagueId 5), but both together (like above) yields no results.

Answer 1

Aren't you grouping by positionid? So for each positionid you're asking the sum for that positionid to be >0 when the id is 4 AND 8 at the same time. Is this possible? Do you want an OR? Should you be using a different query?

GROUP BY LeagueID, for instance?

Answer 2

Your issue is because the SUM can't satisfy both HAVING predicates, so try using an OR:

  SELECT lp.PositionId
    FROM Leagues l 
    JOIN Lineups lp ON (l.LeagueID = lp.LeagueId) 
GROUP BY PositionId
  HAVING SUM(iif(lp.PositionId = 4,1,0)) > 1 OR SUM(iif(lp.PositionId = 8,1,0)) > 0

Answer 3

You can write a much simplified version of this query e.g.

SELECT DISTINCT 8 AS PositionId
  FROM Lineups 
 WHERE PositionId = 8 
UNION ALL
SELECT DISTINCT 4
  FROM Lineups 
 WHERE PositionId = 4
HAVING COUNT(*) > 1;