Solved

sql join on same table with different criteria

Posted on 2011-03-17
6
460 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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.
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 viewer will learn how to implement Singleton Design Pattern in Java.
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.

789 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