469,588 Members | 2,879 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,588 developers. It's quick & easy.

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
Nov 22 '05 #1
3 1325
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
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
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.

Similar topics

699 posts views Thread by mike420 | last post: by
23 posts views Thread by middletree | last post: by
7 posts views Thread by Dave | last post: by
4 posts views Thread by Toonman | last post: by
2 posts views Thread by martinharvey via DotNetMonster.com | last post: by
1 post views Thread by iporter | last post: by
2 posts views Thread by Tony K | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.