CODEWITHSUNDEEP
mysql
Michael Jones
Michael Jones

Reputation: 63

Removing rows on outer query when subquery returns null

After the select is executed, I am getting the error "unknown column 'points' in 'where clause'

How can I remove rows from the outer query that contain null records returned from a sub-query? (I need to use the sub-query, as I have another bunch of sub-queries that need to be run to return results I am after)

SELECT  
   s.student_name_first,
   s.student_name_last,
   (
   SELECT sum(i.points)
   FROM incidents i
   WHERE i.student = s.id
   ) AS points
FROM students s
WHERE points IS NOT NULL
GROUP BY s.id

I have also tried, with the same results

SELECT  
   s.student_name_first,
   s.student_name_last,
   (
   SELECT sum(i.points) AS points
   FROM incidents i
   WHERE i.student = s.id AND points IS NOT NULL 
   ) AS p
FROM students s
GROUP BY s.id

thanks in advance

Solution:

What I was looking for was HAVING

SELECT  
   s.student_name_first,
   s.student_name_last,
   (
   SELECT COALESCE(sum(i.incident_points),0)
   FROM incidents i
   WHERE i.student = s.id
   ) as points
FROM students s
GROUP BY s.id
HAVING points > 0

Upvotes: 0

Views: 1373

Answers (1)

Barmar
Barmar

Reputation: 782407

The problem is that you can't use SELECT aliases in the WHERE clause, they have to be in HAVING.

But if you use an inner join, you won't get any null rows in the first place.

SELECT s.student_name_first,
       s.student_name_last,
       SUM(i.points) AS points
FROM students s
JOIN incidents i
ON i.student = s.id
GROUP BY s.id

Upvotes: 2

Related Questions