asked on

sql join on same table with different criteria

I have a language table where there is always a primary language but not a secondary Language. The way I wrote the query, if there are two language records I am getting correct results,:

Select PLanguage.Language as PrimaryLang, SLanguage.Language as SecondLang
from PatientLanguage as PLanguage
left outer join PatientLanguage as SLanguage
on PLanguage.MRN = SLanguage.MRN
where Planguage.MRN = 1003
and PLanguage.LanguageOrder = 1
and isNull(Slanguage.LanguageOrder,2) = 2

this returns,
PrimaryLang SecondLang
English Spanish

but when I change to an MRN who only has a primary language, no record is returned. How do I correct this?

Paul MacDonald

Try:

Select PLanguage.Language as PrimaryLang, ISNULL(SLanguage.Language, "None") as SecondLang
from PatientLanguage as PLanguage
left outer join PatientLanguage as SLanguage
on PLanguage.MRN = SLanguage.MRN
where Planguage.MRN = 1003
and PLanguage.LanguageOrder = 1
and isNull(Slanguage.LanguageOrder,2) = 2

LenTompkins

ASKER

No that didn't work either, I am not returning any row. When I tried this:

Select PLanguage.Language as PrimaryLang, ISNULL(SLanguage.Language, "None") as SecondLang
from PatientLanguage as PLanguage
left outer join PatientLanguage as SLanguage
on PLanguage.MRN = SLanguage.MRN
where Planguage.MRN = 3319
and PLanguage.LanguageOrder = 1

I am getting one row returned, but the data is incorrect
PrimaryLang SecondLang
English English

ASKER CERTIFIED SOLUTION

Paul MacDonald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Paul MacDonald

IF that doesn't work - and my TSQL is rusty - try:

Select PLanguage.Language as PrimaryLang,
ISNULL((Select SLanguage.Language
FROM PatientLanguage as SLanguage
WHERE SLanguage.MRN = 3319
and SLanguage.LanguageOrder = 1), "None") AS SecondLang
FROM PatientLanguage as PLanguage
WHERE PLanguage.MRN = 3319
and PLanguage.LanguageOrder = 1

LenTompkins

ASKER

Great that worked and I got it to work for the whole table by doing this:
Select Planguage.Language as PrimaryLang,
isNull((Select isNull(Slanguage.Language,'None') as SecondLang from PatientLanguage as SLanguage
where SLanguage.LanguageOrder = 2 and PLanguage.MRN = SLanguage.MRN), 'None') as SecLang
from PatientLanguage as PLanguage
where PLanguage.LanguageOrder = 1

Paul MacDonald

Hey, great! I guess I'll have to brush up on my nested queries...