Left outer join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Left outer join

Author

Topic

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2011-06-23 : 13:10:50

Hey,
Just a question...

I have a view that list the organs that are to be reported on. I created this view based on the organ table because some of the organs are not recovered most of the time...ex: Intestine. (it hard to find recipients that match)

What they want is to be able to see all the organs whether recovered or not in a matrix. if they aren't recovered they want to see 0's for the month.

Now my understanding is that LEFT OUTER JOIN will pull everything from that table on only those that match in the corrosponding other table. However it doesn't show me the organs that haven't been recoverd at all. It will only list the organs that have had some type of "disposition" value in it.

the basic SQL code I have is like this:

Select * From vwOrgans LEFT OUTER JOIN tblOrgan ON vwOrgans.Id = tblOrgan.OrganId

Am I wrong to assume that the LEFT OUTER JOIN should work?

Larry

Everyday life brings me back to reality

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-23 : 13:18:55

The posted query ~~should~~ will work in this way:
All rows from the table vwOrgans will appear and all columns coming from tblOrgan will appear with NULL values for those rows with no match in the ON condition.

But maybe there is a WHERE clause or whatever...

No, you're never too old to Yak'n'Roll if you're too young to die.

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2011-06-23 : 13:39:59

Thanks WebFred...Thats what I thought.

When I get home I'll post the entire query and see if you can help me out.

Thanks again!

Everyday life brings me back to reality

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-06-23 : 14:02:59

It sounds like your view has already filtered out the unrecovered organs. Using a LEFT JOIN won't show all of the organs. I think what you want is RIGHT JOIN so that you get all organs and recovery information if it is available.

If your view is only a filtering of the tblOrgan table, then you don't need the view information at all.

=======================================
I have never met a man so ignorant that I couldn't learn something from him. -Galileo Galilei, physicist and astronomer (1564-1642)

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2011-06-23 : 20:50:24

Thanks for all the suggestions! I tried every one of them, the Right outer join seemed to get me the most results but still not all of the organs I'm after.

I think I've narrowed it down though, thanks to you.

The organtype (house all the organs) table is linked to the organrecipient table (this table houses Recipients and what happen to the organs via DispositionOutcomeTypeId (Code below). So if the there isn't a value in the DispositionId, it isn't showing that organ. My next step is contact the develop of the database and get their take and make sure I'm pulling from the correct table. (working from a replication server)

As I typed this and copied the queries over I thought, there is a Patient table where all referrals are housed (donors, recipients, non donors)...I'll look into that but I had already typed this out and didn't feel like deleting it.

I'm open to any suggestions... :)
Larry

This is the view....


Create View OrganOrder
As

Select ot.id, ot.ShortName

From OrganType ot

     Where ot.ShortName IN ('Heart','Lung','Double Lung','Heart/Lung','Liver',
                      'Liver Segment', 'Liver Segment 1', 'Liver Segment 2',
                      'Left Kidney', 'Right Kidney', 'En-Bloc Kidney',
                      'Pancreas', 'Intestine','Right Lung', 'Left Lung', 'Heart/Lung')

This is the query...

Select blah, blah

FROM         OrganOrder AS ot LEFT OUTER JOIN
             OrganRecipient AS orgrec ON orgrec.DispositionOrganTypeId = ot.Id LEFT OUTER JOIN
             DonorReferral AS dr ON orgrec.CaseFileId = dr.ReferralId

Everyday life brings me back to reality

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2011-06-24 : 16:32:50

Okay, so I completely re did the query and I get the results I want when not "grouped" by year. And here is why, I have to have some date to group these on so I took the "created on" date as to when a recipient was entered in the data base...only problem now is if there isn't a "created on" date for a particular organ it gets left out of the result. If I put a WHERE @Year in there then the ‘Heart/Lung’ won’t show, cause it’s never been ‘created'.

From

Organtype ot Left Outer Join
OrganRecipient orgrec ON ot.Id = orgrec.DispositionOrganTypeId LEFT OUTER JOIN
TransplantPatient AS tp ON orgrec.TransplantPatientId = tp.Id LEFT OUTER JOIN
patient rp ON rp.ID = tp.PatientId LEFT OUTER JOIN
Referral AS r ON rp.Id = r.PatientId

Where ot.ShortName IN ('Heart','Lung','Double Lung','Heart/Lung','Liver',
'Liver Segment', 'Liver Segment 1', 'Liver Segment 2',
'Left Kidney', 'Right Kidney', 'En-Bloc Kidney',
'Pancreas', 'Intestine','Right Lung', 'Left Lung', 'Heart/Lung')
----AND YEAR(tp.CreatedOn) = '2008'

Group By
ot.ShortName, YEAR(tp.CreatedOn)

Everyday life brings me back to reality

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2011-06-24 : 16:34:26

So how would I get that to show? The "Heart/Lung".

Everyday life brings me back to reality

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources