filter NULL values from query

Question

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.

TehShrike · Accepted Answer · 2020-07-09 15:24:28Z

5

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

edited Jul 9, 2020 at 15:24

answered Sep 3, 2011 at 20:14

TehShrike

10.1k2 gold badges36 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Janick Bernet · Accepted Answer · 2011-09-03 19:14:39Z

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).

I check the query but I don't get correct result ( I get repeatable rows). I update the question with the result of my original query.

Paul Roub · Accepted Answer · 2018-03-26 20:28:31Z

0

SELECT * FROM user where name=""

edited Mar 26, 2018 at 20:28

Paul Roub

36.5k27 gold badges88 silver badges95 bronze badges

answered Mar 26, 2018 at 20:19

Charlie

3594 silver badges8 bronze badges

Collectives™ on Stack Overflow

filter NULL values from query

3 Answers 3

Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Related