Solved

sql join on same table with different criteria

Posted on 2011-03-17
6
444 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
  • 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
powershell and sql server - alerting 7 77
BULK LOGGED - log full 9 25
DATETIMEOFFSET feature 1 33
user defined date datatype in SQL Server- can it be overdone.. 6 24
This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now