Sql query getting name column null

Question

Sql fiddle

CREATE TABLE [Users_Reg]
(
   [User_ID]        [int] IDENTITY (1, 1) NOT NULL CONSTRAINT User_Reg_P_KEY PRIMARY KEY,
   [Name]           [varchar] (50) NOT NULL, 
   [Type]           [varchar] (50) NOT NULL /*Technician/Radiologist*/
)

CREATE Table [Study]
(
  [UID]             [INT] IDENTITY (1,1) NOT NULL CONSTRAINT Patient_Study_P_KEY PRIMARY KEY,
  [Radiologist]     [int], /*user id of Radiologist type*/
  [Technician]      [int], /*user id of Technician type*/

)


select * from Study
inner join Users_Reg
on Users_Reg.User_ID=Study.Radiologist

In patient_study table may be Radiologist or Technician have 0 value. how i get technician name and radiologist name from query.

Answer 1

You will want to JOIN on the users_reg table twice to get the result. Once for the radiologist and the another for the technician:

select ps.uid,
  ur1.name rad_name,
  ur1.type rad_type,
  ur2.name tech_name,
  ur2.type tech_type
from Patient_Study ps
left join Users_Reg ur1
  on ur1.User_ID=ps.Radiologist
left join Users_Reg ur2
  on ur2.User_ID=ps.Technician;

See SQL Fiddle with Demo. This will return both the radiologist and technician name/type for all patient studies. If you want to replace the null in any of the columns, then you could use COALESCE similar to the following:

select ps.uid,
  coalesce(ur1.name, '') rad_name,
  coalesce(ur1.type, '') rad_type,
  coalesce(ur2.name, '') tech_name,
  coalesce(ur2.type, '') tech_type
from Patient_Study ps
left join Users_Reg ur1
  on ur1.User_ID=ps.Radiologist
left join Users_Reg ur2
  on ur2.User_ID=ps.Technician;

See SQL Fiddle with Demo

Answer 2

in your case you could use isnull:

select * 
from Patient_Study as PS
    inner join Users_Reg as U on U.User_ID = isnull(nullif(PS.Radiologist, ''), PS.Technician)

or two left outer joins:

select PS.*, isnull(UP.Type, UT.Type) as Type
from Patient_Study as PS
  left outer join Users_Reg as UP on UP.User_ID = PS.Radiologist
  left outer join Users_Reg as UT on UT.User_ID = PS.Technician;

SQL FIDDLE with example.

As advice I might say - store null values in your table if you have no proper value

INSERT INTO Patient_Study(Radiologist,Technician)VALUES('1','2')
INSERT INTO Patient_Study(Radiologist,Technician)VALUES('1','')
INSERT INTO Patient_Study(Radiologist,Technician)VALUES(null,'2')