Outer Join Blues

Question

Post reply

Outer Join Blues

zuma01

SSC Eights!

Points: 943
More actions
October 9, 2009 at 3:28 am

#136272

Hi Guys,
OK, pretty new to TSQL as you will discover. I am struggling with this one so any help appriciated.
I have 2 tables as seen below and I am trying to join them. I know salesline has 3 records I am interested in and CUSTINVOICEJOUR as 3 related records but one of them has no salesid.
As I understand it the query using the left outer join should bring back 3 records and just have null values in any column that is has no data in the salestable. Please advise, appricaite your time
Jason
SELECT *
FROM CUSTINVOICEJOUR
LEFT OUTER JOIN SALESLINE ON SALESLINE.SALESID = dbo.CUSTINVOICEJOUR.SALESID
WHERE CUSTINVOICEJOUR.DATAAREAID = 'GAL'
AND SALESLINE.DATAAREAID = 'GAL'
AND CUSTINVOICEJOUR.invoiceaccount = 'sa13039'

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

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Try this

SELECT *

FROM CUSTINVOICEJOUR

LEFT OUTER JOIN SALESLINE ON SALESLINE.SALESID = dbo.CUSTINVOICEJOUR.SALESID

and SALESLINE.DATAAREAID = 'GAL'

WHERE CUSTINVOICEJOUR.DATAAREAID = 'GAL'

AND CUSTINVOICEJOUR.invoiceaccount = 'sa13039'

The where clause is processed after the join and filtering out the null values

Clear Sky SQL
My Blog[/url]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Dave has supplied the answer. Not to pimp my blog, but I recently blogged about this issue and the post may help you, http://wiseman-wiseguy.blogspot.com/2009/09/on-vs-where.html

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Garadin One Orange Chip Points: 29613 More actions · Answer 3

"pimp my blog". Nice. :w00t:

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Garadin (10/9/2009)
"pimp my blog". Nice. :w00t:

I'm not sure what else to call it when I reference my own blog. I feel kind of dirty doing it, but I think it's a good post and it applies.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

Garadin (10/9/2009)
--------------------------------------------------------------------------------
"pimp my blog". Nice.
Jack Corbett (10/9/2009)
I'm not sure what else to call it when I reference my own blog. I feel kind of dirty doing it, but I think it's a good post and it applies

And it is a good answer to the OP's question and an easy read ... be proud of it, no need to pimp it just say "read this item"

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Garadin One Orange Chip Points: 29613 More actions · Answer 6

Jack Corbett (10/9/2009)
Garadin (10/9/2009)
"pimp my blog". Nice. :w00t:
I'm not sure what else to call it when I reference my own blog. I feel kind of dirty doing it, but I think it's a good post and it applies.

Indeed, I don't know why you'd feel dirty at all. That's the entire purpose of a technical blog. The fact that you are writing things that will actually help people should make you feel better about the time you spend doing it. After all, it's only if nobody benefits from your blog or reads these articles you write that it's truly a waste of time. I just liked the phrase =).

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

Thanks for the support guys. I'm just uncomfortable with self-promotion. Of course, until I have 1000's of readers I guess I have to.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 8

Jack Corbett (10/9/2009)
..until I have 1000's of readers I guess I have to.

Count me in Jack 🙂

Piotr

...and your only reply is slàinte mhath

dji Ten Centuries Points: 1229 More actions · Answer 9

Sorry for bringing an old thread back to the top but this is a really useful thread. The information gleaned from reading is most helpful. Many thanks.