February 5, 2016 at 3:30 pm
I am trying to eliminate the NULL and duplicate values. I have 3 type Id's that represent different fields in one table but same column.
I want to be able to have all 3 types on one row. I don't want to see the NULL values
This is my query:
SELECT DISTINCT(case when a.Type_Id = 6 then a.Description end) As PROD,
case when a.Type_Id = 98 then a.Description end As R,
case when a.Type_Id = 120 then a.Description end As OWNER,
b.Client_Id as ClientID,
C.TextCol as NOTE
from AMGR_User_Field_Defs A
inner join AMGR_User_Fields B on a.Type_Id = b.Type_Id and a.Code_Id = b.Code_Id
inner join AMGR_Client D on d.Client_Id = B.Client_Id
inner join AMGR_Notes C on b.Client_Id = c.Client_Id
WHERE A.Type_Id = B.Type_Id
AND A.Code_Id = B.Code_Id
This is the result:
PROD|R |OWNER|CLIENTID |NOTE
ENB|NULL| NULL |127398273C|NOTE1
NULL|R1 | NULL |127398273C|NOTE1
NULL|NULL| SMITH|127398273C|NOTE1
But I want the result to be like this:
PROD|R |OWNER|CLIENTID |NOTE
ENB|R1 | SMITH |127398273C |NOTE1
Please help!!
February 5, 2016 at 4:08 pm
Try this:
SELECT
max(case when a.Type_Id = 6 then a.Description end) As PROD,
max(case when a.Type_Id = 98 then a.Description end) As R,
max(case when a.Type_Id = 120 then a.Description end) As OWNER,
b.Client_Id as ClientID,
C.TextCol as NOTE
from
AMGR_User_Field_Defs A
inner join AMGR_User_Fields B on a.Type_Id = b.Type_Id and a.Code_Id = b.Code_Id
inner join AMGR_Client D on d.Client_Id = B.Client_Id
inner join AMGR_Notes C on b.Client_Id = c.Client_Id
WHERE
A.Type_Id = B.Type_Id
AND A.Code_Id = B.Code_Id
GROUP BY
b.Client_Id,
C.TextCol;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply