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

[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

[Greg Patnude]

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
[phone]

"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

[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;

[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