Outer join doubt

[Manuel Espinoza]

Hi!

I'm using SQLAnywhere 9.0.1. 1873 on Win XP and have a query that does not
work as I mean it to do :-(
I've tested many ways of doing the joins, but no luck. Let's say I have 3
tables, A, B and C. The point here is that I always must show all the
content of A, no matter if there is correlation with B or C or not. So, I
did the following:

Select *
from A left outer join B RV on A.id_x = B.id_x,
C
Where B.id_b = C.id_b and
and B.id_z Between 1 and 1
and B.id_b Between 1 and 100

Which shows correctly, but if I change it for, let's say, B.id_b between 2
and 2 it only shows 1 record(which is the id_b = 2).

Also tested this:

Select *
from C left outer join (A left outer join B RV on A.id_x = B.id_x)
on B.id_b = C.id_b
Where B.id_z Between 1 and 1
and B.id_b Between 1 and 100

And again, shows OK until I ask for id_b between 2 and 2.

I was reading the SA's help about joins and so on, but I can't figure out
how to do it.

Any idea?

Many thanks in advance

[Glenn Paulley [Sybase]]

By placing the additional conditions in the WHERE clause, you are
effectively turning the outer join into an inner joins. The order of
evaluation of SQL (logically) is to first evaluate the table expressions
in the FROM clause, and THEN apply any selection conditions in the WHERE
clause.

If I understand your intentions correctly, you want to use a derived
table, as follows (untested):

Select *
from A left outer join (
( SELECT * FROM B
WHERE B.id_z Between 1 and 1
and B.id_b Between 1 and 100 ) as DT
JOIN C ON DT.id_b = C.id_) ON A.id_x = DT.id_x

Glenn

--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/databasemanagement/sqlanywhere/technicalsupport

[Manuel Espinoza]

Thanks a lot!

Derived table is the answer!

"Glenn Paulley [Sybase]" <pau...@ianywhere.com> escribi� en el mensaje
news:48e60388@forums-1-dub...