Solved

sql join on same table with different criteria

Posted on 2011-03-17
6
472 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

If you haven’t already, I encourage you to read the first article (http://www.experts-exchange.com/articles/18680/An-Introduction-to-R-Programming-and-R-Studio.html) in my series to gain a basic foundation of R and R Studio.  You will also find the …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.

728 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