Inner joins in sql

Amudhan Kuppan

8y944SQL

i have one doubt please help me

create table Patient(

ID int not null primary key,

FirstName varchar(30),

LastName varchar(30),

DateOfBirth datetime

);

Create table Insurance(

ID int not null Primary key,

InsuranceName varchar(50),

);

Create table PatientInsurance(

PatientID int not null,

InsuranceID int not null,

Foreign Key(PatientID) References patient(ID),

Foreign Key(InsuranceID) References Insurance(ID)

);

there are three tables in my db and this is a table structure

iam insert a five record for each table ok

my question is : How to get a list of all patients having insurancename and patient does not have any insurance

how to prepare a script for this question please help me anyone

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
Accepted answer
Here is a query which should satisfy the requirement.
select p.[FirstName]
  ,p.[LastName]
  ,p.[DateOfBirth]
  ,isnull(i.[InsuranceName], 'Not insured') [InsuranceName]
from [dbo].[Patient] p
left outer join [dbo].[PatientInsurance] pi on p.[Id] = pi.[PatientId]
left outer join [dbo].[Insurance] i on pi.[InsuranceId] = i.[Id]
Note that all the patients will be listed. Patients having more than one insurer will be listed twice.
I BEG YOU not to accept the answer unless you are FULLY SATISFIED.

PLEASE DO NOT RUSH TO ACCEPT THE ANSWER.

Thank you.
Amudhan Kuppan8y ago
hi shahriar
but in my senior told
I.PATIENTID IS NULL REMOVE THIS CONDITION
you will get both all patients who is having insurance and not having insurance
see my requiremen
In result I need patientid,patientname, insurancename
suppose iam remove that condition ok i will get only id,firstname,lastname,dob
it is right please suggest me iam confused in that task
Amudhan Kuppan8y ago
thank you shahriar
Shahriar Nikkhah8y ago
the answer is the list of the patient with insurance i dont see the Patient without insurance
SELECT p.* FROM  patient p LEFT JOIN PatientInsurance PI

ON PI.PatientID = p.PatientID

WHERE  PI.PatientID IS NULL

LEFT JOIN wa missing in one of the answers
Amudhan Kuppan8y ago
select Patient.ID,patient.FirstName,patient.LastName,patient.DateOfBirth,insurance.InsuranceName from patient inner join
insurance on patient.ID = Insurance.ID

this query is also right or any mistakes sankara krishnan
Amudhan Kuppan8y ago
Ok nolock and PIS means i cannot understand PIS means P IS right