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(at)wildbrain(dot)com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster