Re: Odd query behavior...

Hi,

To see what you are doing, try

SELECT * FROM table1, table2;

You are SELECTing from the CROSS PRODUCT of both tables.

You inserted:
table       rows
TUser       2
TGroup      2
TUnrelated 0

So the cross product for the 2 first tables gives 2x2=4 rows.
The cross product of TUnrelated with any other will be 0x?=0.

Good luck.

Antonio


Gregory Brauer wrote:

> I have a question about the behavior of SELECT with multiple
> tables.  Take this example... 4 tables, 2 related by 1 many-to-many
> table, and 1 unrelated table:
>
> CREATE TABLE TGroup (
>      id SERIAL PRIMARY KEY,
>      name VARCHAR(16) UNIQUE
> );
>
> CREATE TABLE TUser (
>      id SERIAL PRIMARY KEY,
>      username VARCHAR(16) UNIQUE,
>      password VARCHAR(16)
> );
>
> CREATE TABLE TUnrelated (
>      id SERIAL PRIMARY KEY,
>      something VARCHAR(16) UNIQUE
> );
>
> CREATE TABLE TGroupTUserLink (
>      tgroup_id int4 NOT NULL,
>      tuser_id int4 NOT NULL,
>      UNIQUE (tgroup_id, tuser_id),
>      UNIQUE (tuser_id, tgroup_id),
>      FOREIGN KEY (tgroup_id) REFERENCES TGroup(id) ON DELETE CASCADE,
>      FOREIGN KEY (tuser_id) REFERENCES TUser(id) ON DELETE CASCADE
> );
>
> INSERT INTO TUser (username, password) VALUES ('bob', 'god');
> INSERT INTO TUser (username, password) VALUES ('fred', 'sex');
> INSERT INTO TGroup (name) VALUES ('user');
> INSERT INTO TGroup (name) VALUES ('luser');
>
> If I do a select from the TUser table, I get what is expected:
>
> test=# select TUser.username from TUser;
>   username
> ----------
>   bob
>   fred
> (2 rows)
>
> However if I do a select from both the TUser and TGroup tables,
> I get a product, even though I have expressed no joins:
>
> test=# select TUser.username from TUser, TGroup;
>   username
> ----------
>   bob
>   bob
>   fred
>   fred
> (4 rows)
>
> And finally, if I do a select from the TUser and TUnrelated tables,
> I get nothing, even though I have specified no joins:
>
> test=# select TUser.username from TUser, TUnrelated;
>   username
> ----------
> (0 rows)
>
> What is going on here?  Why am I getting "products"?  It appears
> to have something to do with how tables are related, but I don't
> understand the full reasoning behind it.  I have done futher tests
> with a table that is related to a table that is related to yet
> another table that I am querying, and I no longer get a product,
> just the results I expect.
>
> Any help would be appreciated...
>
> Thanks.
>
> Greg Brauer
> greg@wildbrain.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster