CODEWITHSUNDEEP
mysqlsql
Kliver Max
Kliver Max

Reputation: 5299

How to filter null values in select?

I have a table:

DataTable:
 id int,
 user_id int,
 field_id int,
 value varchar

DataExample:

id    user_id    field_id   value 
 1       1            1       'Peter'
 2       1            2       56
 3       1            3       100
 4       2            1       'Buzz'
 5       2            2       47
 6       2            3       120
 7       3            1       'Wolf'
 8       3            2        null
 9       3            3        null

I want to separate value column by field_id property:

Name    Age     Score
Peter   56      100
Buzz    47      120

I do:

SELECT id, MAX(Name) AS Name, MAX(Age) AS Age, MAX(Score) AS Score
FROM (
SELECT u.id
 CASE
   WHEN field_id = 1 THEN  value
 END as Name,   
 CASE
    WHEN field_id = 2 THEN  value
 END as Age,
 CASE
   WHEN field_id = 3 THEN  value
 END as Score
 FROM Users u
 INNER JOIN DataTable t ON (t.user_id=u.id)
 ) AS sub
GROUP BY id;

It's works nice. But now i want to filter null values. I mean i dont want to select rows with Age or Score = null. I tried add is not null in CASE blocks but it not helped.
Any advises?

Upvotes: 0

Views: 686

Answers (2)

jarlh
jarlh

Reputation: 44696

Do a double self-join:

select t1.value as name, t2.value as Age, t3.value as Score
from (select userid, value from DataTable where field_id = 1) as t1
join (select userid, value from DataTable where field_id = 2) as t2 ON t1.userid = t2.userid
join (select userid, value from DataTable where field_id = 3) as t3 ON t1.userid = t3.userid

The first sub-query returns names, the second returns ages, and the third scores.

Upvotes: 2

i486
i486

Reputation: 6564

SELECT id, MAX(Name) AS Name, MAX(Age) AS Age, MAX(Score) AS Score
FROM (
SELECT u.id
 CASE
   WHEN field_id = 1 THEN  value
 END as Name,   
 CASE
    WHEN field_id = 2 THEN  value
 END as Age,
 CASE
   WHEN field_id = 3 THEN  value
 END as Score
 FROM Users u
 INNER JOIN DataTable t ON (t.user_id=u.id)
   WHERE value IS NOT NULL -------------------here
 ) AS sub
GROUP BY id;

Upvotes: 0

Related Questions