By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,698 Members | 1,262 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,698 IT Pros & Developers. It's quick & easy.

Repost: Syntax - or unavailability of same - for variable join??? Can anyone help?

P: n/a
Ben
I want to say:

SELECT tableA.stuff,tableB.morestuff,tableC.stillmorestuf f
FROM tableA,
LEFT OUTER JOIN tableB ON (AB match conditions)
LEFT OUTER JOIN tableC ON (AC match conditions)
WHERE etc

However, in some cases, tableB does not have rows where the other two do
(it contains credit card records... but when an order is paid by check, there is
no record.)

What happens with the above syntax is I don't get a row at all.

Is there a way to say that if tableB has no row, I get blank columns?

Maybe I'm just looking at the wrong, but I can't seem to find out how, or
if, one can do this.

Thanks for any input.

--Ben
Nov 22 '05 #1
Share this Question
Share on Google+
3 Replies

P: n/a
You might have better success with the form of HAVING and appropriate use of
OR IS NULL as opposed to strict JOIN and WHERE conditions...

Similar to...

SELECT A.a, B.b, C.c FROM A, B, C
HAVING (A.b = B.b OR B.b IS NULL)
GROUP BY A.b;

--
Greg Patnude / The Digital Demention
2916 East Upper Hayden Lake Road
Hayden Lake, ID 83835
(208) 762-0762

"Ben" <re***@to-the-newsgroup.com> wrote in message
news:4c******************************@news.teranew s.com...
I want to say:

SELECT tableA.stuff,tableB.morestuff,tableC.stillmorestuf f
FROM tableA,
LEFT OUTER JOIN tableB ON (AB match conditions)
LEFT OUTER JOIN tableC ON (AC match conditions)
WHERE etc

However, in some cases, tableB does not have rows where the other two do
(it contains credit card records... but when an order is paid by check, there is no record.)

What happens with the above syntax is I don't get a row at all.

Is there a way to say that if tableB has no row, I get blank columns?

Maybe I'm just looking at the wrong, but I can't seem to find out how, or
if, one can do this.

Thanks for any input.

--Ben

Nov 22 '05 #2

P: n/a
Ben
I'll have a look at that; thank you very much for taking the time to reply. :)

Ben
On Mon, 16 Feb 2004 22:03:22 -0800, Greg Patnude wrote:
You might have better success with the form of HAVING and appropriate use of
OR IS NULL as opposed to strict JOIN and WHERE conditions...

Similar to...

SELECT A.a, B.b, C.c FROM A, B, C
HAVING (A.b = B.b OR B.b IS NULL)
GROUP BY A.b;


Nov 22 '05 #3

P: n/a
Ben
As it turns out, LEFT OUTER JOIN does exactly what I want.

I'm not sure what my original problem was now, though I suspect it was
part of the "where" clause depending on the right side of the join.

So for anyone who might find this thread in a search for this kind of
behaviour, just use LEFT OUTER JOIN. You get the left columns, plus the
right ones where there are records, and NULL for the right columns where
there are no records.

HAVING and GROUP BY have nothing to do with this behaviour, and will lead
you down a dead-end.

Ben
Nov 22 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.