Solved

sql join on same table with different criteria

Posted on 2011-03-17
6
466 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:LenTompkins
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 35157136
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

0
 

Author Comment

by:LenTompkins
ID: 35157232
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
0
 
LVL 34

Accepted Solution

by:
Paul MacDonald earned 500 total points
ID: 35157296
Odd.  How about this:

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

I'll double-check, but that should be syntactically close.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 35157394
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
0
 

Author Closing Comment

by:LenTompkins
ID: 35158349
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
0
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 35158365
Hey, great!  I guess I'll have to brush up on my nested queries...
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article will show, step by step, how to integrate R code into a R Sweave document
This video teaches viewers about errors in exception handling.
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question