470,647 Members | 1,219 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

cross join oddity?

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.
Nov 11 '05 #1
3 2065
Klint Gore wrote:
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.


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.field1, b.*
from a as maintable
cross join b
and put in your restrictions here (ie WHERE maintable.id = b.id)

Ron

Nov 11 '05 #2
rstp <rs**@linuxwaves.com> wrote in message news:<3F**************@linuxwaves.com>...
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.field1, b.*
from a as maintable
cross join b
and put in your restrictions here (ie WHERE maintable.id = b.id)


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

In other DBMS that we use here (mssql,ingres,sybase), 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.
Nov 11 '05 #3
Klint Gore wrote:
rstp <rs**@linuxwaves.com> wrote in message news:<3F**************@linuxwaves.com>...
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.field1, b.*
from a as maintable
cross join b
and put in your restrictions here (ie WHERE maintable.id = b.id)

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

In other DBMS that we use here (mssql,ingres,sybase), 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.

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

Nov 11 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Tim Pascoe | last post: by
5 posts views Thread by jmdocherty | last post: by
15 posts views Thread by Bryce K. Nielsen | last post: by
1 post views Thread by karunajo | last post: by
1 post views Thread by Korara | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.