Re: why does this select hang postgres???

Jeremy Hansen wrote:
>
> On Fri, 10 Aug 2001, Mike Mascari wrote:
>
> > Jeremy Hansen wrote:
> > >
> > > select * from env_info,summary,plat_info;
> > >
> > > Is my statement broken? Is this not legal? Postgres just sits there....
> >
> > Its quite possibly broken. It's legal to PostgreSQL; other databases
> > won't like the missing FROM clause. But PostgreSQL will generate the
> > Cartesian product of all three tables. If table 1 has 100 rows,
> > table 2 has 100 rows, and table 3 has 100 rows, PostgreSQL will have
> > to construct and return 100 * 100 * 100 rows = 1,000,000 rows.
> > >
> > > My goal is to select everything from multiple tables with one sql
> > > statement.
> >
> > Are you sure you want each row of each table combined with each row
> > of each of the other tables? If these tables are related in any way
> > you have to provide a WHERE clause that specifies the relationship.
>
> Ok, I limited this using a where clause and it's still killing postgres:
>
> select * from summary,env_info,plat_info where summary.bug_id=81;

I'm going to refer you to Bruce Momjian's book. Here's the chapter
on "Joining Tables":

http://www.ca.postgresql.org/docs/aw_pgsql_book/node55.html

I'm guessing you need to further qualify your query. Again, if table
1 has 100 rows and table 2 has 100 rows and table 3 has 10 rows
matching summary.bug_id = 81, then you'll still get 100,000 rows
returned. Say I want the an employees' name, salary, and any time
they took vacation for *all* employees. I would write the query
like:

SELECT * FROM employees, salaries, vacations
WHERE employees.employeeid = salaries.employeid AND
employees.employeeid = vacations.employeeid;

If I wanted to qualify it further, say to fetch all the above
information for employee #32, I'd write:

SELECT * FROM employees, salaries, vacations
WHERE employees.employeeid = salaries.employeeid AND
employees.employeeid = vacations.employeeid AND
employees.employeeid = 32;

Please read Bruce's book as it will solve all your problems and
answer all your questions.

Mike Mascari
mascarm(at)mascari(dot)com