How to eliminate NULL values from the query result?-CodePudding

Currently I'm trying to write a single SQL query to produce some intended result, I managed to do it but there are some null values in my results and I'm just wondering how can I get rid of them.

Here is the database structure:

Here is my query:

SELECT ut.username,
       (case when FieldID = 1 then Data end) as phone,
       (case when FieldID = 2 then Data end) as email
FROM usertable ut
JOIN userdata ud
ON ut.ID = ud.userID;

Here is my result:

Here is the intended result:

Any suggestions are welcomed!

CodePudding user response：

Use a pivot to get just one row per user.

SELECT ut.username,
       MAX(case when FieldID = 1 then Data end) as phone,
       MAX(case when FieldID = 2 then Data end) as email
FROM usertable ut
JOIN userdata ud
ON ut.ID = ud.userID
GROUP BY ut.username;

CodePudding user response：

Try (not verified), join userdata twice, one for email, one for phone:

SELECT ut.username,
       udp.Data as phone,
       udm.Data as email
FROM usertable ut
LEFT JOIN userdata udm
ON ut.ID = udm.userID AND FieldID = 2
LEFT JOIN userdata udp
ON ut.ID = udp.userID AND FieldID = 1;

CodePudding user response：

use MAX

    SELECT ut.username,
   MAX(case when FieldID = 1 then Data end) as phone,
   MAX(case when FieldID = 2 then Data end) as email

FROM usertable ut JOIN userdata ud ON ut.ID = ud.userID GROUP BY ut.username;