By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,303 Members | 1,239 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,303 IT Pros & Developers. It's quick & easy.

cross join oddity?

P: n/a
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
Share this Question
Share on Google+
3 Replies

P: n/a
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

P: n/a
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

P: n/a
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.