mysql – I don't know how to prevent it from showing me records with fields in NULL

Question:

I am solving a problem in MySql where I have to exchange table values ​​in pairs. The problem I have practically solved, the issue is that it shows me the records with the student field NULL. I know that to omit those records I would have to put the name of the field where IS NOT NULL. The problem with this is that I don't know how to reference that field. I tried with the alias but I get a syntax error and I also tried with s1.student and s2.student and it doesn't work either. If anyone knows how to avoid showing me the fields with null I would appreciate it.

    SELECT DISTINCT s1.id, 
CASE 
    WHEN s2.id%2 = 0 AND s1.id = s2.id-1 THEN s2.student
    WHEN s1.id%2 = 0 AND s1.id = s2.id+1 THEN s2.student
    WHEN s1.id = (SELECT MAX(id) FROM seat) AND s1.id%2 != 0 THEN s1.student
    ELSE NULL
END
AS student
FROM seat s1, seat s2 WHERE (aca es donde nose que poner) IS NOT NULL ORDER BY s1.id

---

I do not get any error, the problem is that it omits the instruction s1.student is not null . And the table of the results shows me the records with NULL values

Also WHERE s1.student IS NOT NULL AND s2.student IS NOT NULL with: WHERE s1.student IS NOT NULL AND s2.student IS NOT NULL but it keeps showing me records with the student field in NULL.

Well there I could find a solution , and that is to refer to the field with the entire case block. It is very long but it worked for me. I would still like to know if you can refer to that block of the case in a more effective and short way. So the code is:

   SELECT DISTINCT s1.id, 
CASE 
  WHEN s2.id%2 = 0 AND s1.id = s2.id-1 THEN s2.student
  WHEN s1.id%2 = 0 AND s1.id = s2.id+1 THEN s2.student
  WHEN s1.id = (SELECT MAX(id) FROM seat) AND s1.id%2 != 0 THEN s1.student
  ELSE NULL
END
AS student
FROM seat s1, seat s2 
WHERE (CASE 
  WHEN s2.id%2 = 0 AND s1.id = s2.id-1 THEN s2.student
  WHEN s1.id%2 = 0 AND s1.id = s2.id+1 THEN s2.student
  WHEN s1.id = (SELECT MAX(id) FROM seat) AND s1.id%2 != 0 THEN s1.student
  ELSE NULL
END) IS NOT NULL 
ORDER BY s1.id

Answer:

Since it is a calculated column and in the WHERE clause we can only refer to columns of the FROM tables, you can wrap your query in a SELECT and filter:

SELECT *
  FROM ( tu_query ) tmp
  WHERE student is not null

Another option is to use the HAVING clause where MySQL allows us to reference calculated columns without the need for a GROUP BY :

FROM seat s1, seat s2
HAVING student is not null
ORDER BY s1.id