cross join oddity?

Re: cross join oddity?

Klint Gore wrote:[color=blue]
> I've hit a situation where I'm getting an unexpected set of results
> from a cross
> join that I've narrowed down to a table alias.
>
> If I do (a has 4 rows, b has 1 row)
> select a.field1, b.*
> from a as maintable
> cross join b
>
> I get 16 rows returned. Is this expected behaviour or should an error
> have been raised with the a. reference in the field list? postgres
> 7.2.3 on linux. (mssql and ingres both raise an error)
>
> klint.[/color]

It looks like each element in a is being matched up with each in
maintable. If you want to use the alias maintable, perhaps you should
write the query as
select maintable.field 1, b.*
from a as maintable
cross join b
and put in your restrictions here (ie WHERE maintable.id = b.id)

Ron

Re: cross join oddity?

rstp <rstp@linuxwave s.com> wrote in message news:<3F213320. 8000607@linuxwa ves.com>...[color=blue]
> It looks like each element in a is being matched up with each in
> maintable. If you want to use the alias maintable, perhaps you should
> write the query as
> select maintable.field 1, b.*
> from a as maintable
> cross join b
> and put in your restrictions here (ie WHERE maintable.id = b.id)[/color]

I did it that way when I got the incorrect result set.

In other DBMS that we use here (mssql,ingres,s ybase), they all return
an error if you use the table name instead of the alias. I would have
expected postgres to do the same. So my question is more should it
raise an error rather than how to fix it?

klint.

Re: cross join oddity?

Klint Gore wrote:[color=blue]
> rstp <rstp@linuxwave s.com> wrote in message news:<3F213320. 8000607@linuxwa ves.com>...
>[color=green]
>>It looks like each element in a is being matched up with each in
>>maintable. If you want to use the alias maintable, perhaps you should
>>write the query as
>> select maintable.field 1, b.*
>> from a as maintable
>> cross join b
>>and put in your restrictions here (ie WHERE maintable.id = b.id)[/color]
>
>
> I did it that way when I got the incorrect result set.
>
> In other DBMS that we use here (mssql,ingres,s ybase), they all return
> an error if you use the table name instead of the alias. I would have
> expected postgres to do the same. So my question is more should it
> raise an error rather than how to fix it?
>
> klint.[/color]


I don't think that it should raise an error as there are some instances
where you would want to access a table twice within one query, with an
alias and without. Eg to get information from tblUsers (fName, lName,
inputBy) where inputBy is another user in the table.

Ron