How can I use same table and same column under different condition in SQL Server?-CodePudding

I have a database named PatientInfoDB. In this database, I have four tables.
Here are the tables:

Patient Table

PatientId    RegNo     Name    GenderId   BloodGroupId
1            EM-001    John       1           4            
2            EM-002    Wick       1           3
3            EM-003    Natasha    2           4

EmployeeInfo Table

EmployeeInfoId   PatientId    Grade    DesignationId    WorkPlaceId
1                   1         12             6              7
2                   3         10             5              8
3                   2         10             6              8

SysParams Table

SysParamId      Name 
1               Gender
2               BloodGroup
3               Designation
4               WorkPlace

Params Table

ParamId   Name       SysParamId
1         Male           1
2         Female         1
3         A              2
4         O              2
5         Manager        3
6         Nurse          3
7         London         4
8         Manchestar     4

Now I want make a report using SSRS, where I want to show
PatientName, PatientRegNo, Gender, BloodGroup, Designation and WorkPlace

https://prnt.sc/HxEF8Y7AkEDW And I get this query:

SELECT        Patients.Name, Patients.RegNo, PatientEmployeeInfos.DesignationId, PatientEmployeeInfos.WorkPlaceId, Patients.GenderId, Patients.BloodGroupId, Params.Name AS Expr1
FROM            Patients INNER JOIN
                         PatientEmployeeInfos ON Patients.PatientId = PatientEmployeeInfos.PatientId INNER JOIN
                         Params ON Patients.BloodGroupId = Params.ParamId AND Patients.CountryId = Params.ParamId AND Patients.GenderId = Params.ParamId AND Patients.MaritalStatusId = Params.ParamId AND 
                         PatientEmployeeInfos.DepartmentId = Params.ParamId AND PatientEmployeeInfos.WorkPlaceId = Params.ParamId INNER JOIN
                         SysParams ON Params.SysParamId = SysParams.SysParamId

I want something like this:

PatientName   PatientRegNo   Gender  BloodGroup  Designation  WorkPlace
John           EM-001        Male      O           Nurse       London
Wick           Em-002        Male      A           Nurse       Manchestar
Natasha        Em-003        Female    O           Manager     Manchestar

How can I make the SQL query to show the data from same table and same column under different conditions?

CodePudding user response：

For each of the columns that get their values from Params table i.e Gender, BloodGroup, Designation and Workplace, you'll have to add a join statement. The query will look something like below

select 
  pat.name, 
  pat.reg_no,
  gender.name as gender, 
  bloodgroup.name as bloodgroup,
  designation.name as designation,
  workplace.name as workplace
from patient pat
inner join 
  params gender on gender.param_id = pat.gender_id and gender.sys_param_id = 1
inner join
  params bloodgroup on bloodgroup.param_id = pat.blood_group_id and bloodgroup.sys_param_id = 2
inner join 
  employee_info emp on emp.patient_id = pat.patient_id
inner join 
  params designation on designation.param_id = emp.designation_id and designation.sys_param_id = 3
inner join 
  params workplace on workplace.param_id = emp.workplace_id and workplace.sys_param_id = 4
;

Notice that in each of the joins to Params table we also add a condition to filter on the required sys_param. For gender we say look for the param that matches the param_id in patient and the sys_param_id should be 1 which we know is for gender. Similarly we get the value for bloodgroup

Designation and Workplace is stored in EmployeeInfo so we join that first. To get the name, similar strategy is used here but rather than matching param_id from Patient table we do it from EmployeeInfo

I hope it makes sense. Would be happy to clarify any confusion