filter NULL values from query

In the below query I want only to saw the rows that dont have NULL values in risk column

SELECT table1.id, table1.name,
   CASE 
     WHEN table1.event = 'r' AND table1.name = 'jones' THEN 'very high'
     WHEN table1.event = 't' AND table1.name = 'smith' THEN 'very low'
     ELSE (SELECT table2.risk FROM table2 WHERE table2.value <= table1.value
           ORDER BY table2.value DESC LIMIT 1)
   END AS risk
FROM table1
ORDER BY FIELD( table1.event, 'r', 'f', 't' ), table1.value DESC

thanks.

The result of the above query is:

id  name            risk
17  name            very low
17  name            NULL
18  qwert yuiop very low
18  qwert yuiop NULL
19  qwert yuiop very low
19  qwert yuiop NULL
20  qwert yuiop very low
20  qwert yuiop NULL
21  qwert yuiop very low
21  qwert yuiop NULL
22  q           very low
22  q           NULL
23  q           very low
23  q           NULL
24  q           very low
24  q           NULL
25  q           very low
25  q           NULL
26  q   开发者_运维百科        very low
26  q           NULL

And I want to get rid of the NULL's.

---

You should just be able to filter out those rows with a HAVING clause:

SELECT table1.id, table1.name,
  CASE
    WHEN table1.event = 'r' AND table1.name = 'jones' THEN 'very high'
    WHEN table1.event = 't' AND table1.name = 'smith' THEN 'very low'
    ELSE (
      SELECT table2.risk 
      FROM table2 WHERE table2.value <= table1.value 
      ORDER BY table2.value DESC 
      LIMIT 1
    )
  END AS risk
FROM table1
-- Add this row:
HAVING risk IS NOT NULL
ORDER BY FIELD( table1.event, 'r', 'f', 't' ), table1.value DESC

---

SELECT id, name, risk FROM (
  SELECT table1.id, table1.name,
   CASE 
     WHEN table1.event = 'r' AND table1.name = 'jones' THEN 'very high'
     WHEN table1.event = 't' AND table1.name = 'smith' THEN 'very low'
     ELSE (SELECT table2.risk FROM table2 WHERE table2.value <= table1.value
           ORDER BY table2.value DESC LIMIT 1)
   END AS risk
  FROM table1
  ORDER BY FIELD( table1.event, 'r', 'f', 't' ), table1.value DESC
) T
WHERE risk IS NOT NULL

Update: I just realized this is MySQL and not a generic SQL question, so I'm not 100% sure this subquery syntax is supported by MySQL. So an MySQL approach:

  SELECT table1.id, table1.name,
   CASE 
     WHEN table1.event = 'r' AND table1.name = 'jones' THEN 'very high'
     WHEN table1.event = 't' AND table1.name = 'smith' THEN 'very low'
     ELSE (SELECT table2.risk FROM table2 WHERE table2.value <= table1.value
           AND table2.risk IS NOT NULL
           ORDER BY table2.value DESC LIMIT 1)
   END AS risk
  FROM table1
  ORDER BY FIELD( table1.event, 'r', 'f', 't' ), table1.value DESC

So the filtering is done earlier on, which might use indexes (though a good optimizer would transfer the first Query into the second).

---

SELECT * FROM user where name=""