Left join

On a Sql Server 2005, suppose the query

SELECT ......
FROM A
LEFT JOIN B ON B.Col1 = A.Col2
LEFT JOIN C ON (C.Col3 = A.Col4 OR C.Col5 = B.Col6)

If the left join on B results in 1 or more rows, then I get all the
rows from C: the rows that apply to the first condition and the rows
that apply to the second condition.

If the left join on B results in 0 rows, then I get no rows from C, not
even the ones for which C.Col3 = A.Col4 applies.

How can rewrite this query, so that of there are not B rows applying to
the query, I still get the C rows for C.Col3 = A.Col4? I don't want to
left join with C twice. Is it possible at all to do such a thing?

Last time I had a problem like this one, I used a subquery to first left
join A and B and then used this subquery to left join with C.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


wrote in message

> On a Sql Server 2005, suppose the query
>
> SELECT ......
> FROM A
> LEFT JOIN B ON B.Col1 = A.Col2
> LEFT JOIN C ON (C.Col3 = A.Col4 OR C.Col5 = B.Col6)
>
> If the left join on B results in 1 or more rows, then I get all the
> rows from C: the rows that apply to the first condition and the rows
> that apply to the second condition.
>
> If the left join on B results in 0 rows, then I get no rows from C, not
> even the ones for which C.Col3 = A.Col4 applies.
>
> How can rewrite this query, so that of there are not B rows applying to
> the query, I still get the C rows for C.Col3 = A.Col4? I don't want to
> left join with C twice. Is it possible at all to do such a thing?
>