Right outer Join???

Question

Right outer Join???

jbalbo

SSCertifiable

Points: 7583
More actions
August 1, 2013 at 12:25 pm

#278166

I know I'm lost somewhere here but I thought this would get me all client in Rday_All_clients even if a record didn't exist in R_Day ?
SELECT Rday_All_clients.LName, Rday_All_clients.FName, R_Day.OID, R_Day.STARTTIME,
FROM R_Day RIGHT OUTER JOIN
Rday_All_clients ON R_Day.ID = Rday_All_clients.Client_ID
WHERE (R_Day.OID = '140FA7F3F5924873B8A14E369A3CCCBD') AND (R_Day.CAD69 = '1')
ORDER BY Rday_All_clients.LName

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

The problem is that your WHERE clause is converting the OUTER JOIN into an INNER JOIN. Change those conditions to the ON clause and it should work as expected.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

jbalbo SSCertifiable Points: 7583 More actions · Answer 2

Thanks so much.. wow I have sooo much to learn...

never thought of using an on with more than one field...

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Also, you might consider reversing your code and making this a left join. Many people struggle with the concept of a right join because it is backwards for most people.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jbalbo SSCertifiable Points: 7583 More actions · Answer 4

Thanks Sean.. good idea

Its one thing I think I do get..

At least I think I do.. lol

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

jbalbo (8/1/2013)
Thanks Sean.. good idea
Its one thing I think I do get..
At least I think I do.. lol

It is exactly like a left join....only backwards. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 6

Sean Lange (8/1/2013)
jbalbo (8/1/2013)
Thanks Sean.. good idea
Its one thing I think I do get..
At least I think I do.. lol
It is exactly like a left join....only backwards. 😛

I wonder if left handed people find right outer joins easier. :unsure:

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Alvin Ramard (8/1/2013)
Sean Lange (8/1/2013)
jbalbo (8/1/2013)
Thanks Sean.. good idea
Its one thing I think I do get..
At least I think I do.. lol
It is exactly like a left join....only backwards. 😛
I wonder if left handed people find right outer joins easier. :unsure:

I think they make a left handed version of SSMS for right join folks. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jbalbo SSCertifiable Points: 7583 More actions · Answer 8

I guess you'd be all set if you were ambidextrous...

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 9

jbalbo (8/1/2013)
I guess you'd be all set if you were ambidextrous...

Full outer joins? :w00t:

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Voide Mr or Mrs. 500 Points: 548 More actions · Answer 10

Alvin Ramard (8/1/2013)
jbalbo (8/1/2013)
I guess you'd be all set if you were ambidextrous...
Full outer joins? :w00t:

I wish this just returned a random value just to mess with people who typed it.