Solved

sql join on same table with different criteria

Posted on 2011-03-17
6
454 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The goal of this video is to provide viewers with basic examples to understand and use conditional statements in the C programming language.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

803 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