Simplyfying many equals in a join

From: Randall Skelton <skelton(at)brutus(dot)uwaterloo(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: Simplyfying many equals in a join
Date: 2004-02-26 01:44:40
Message-ID: Pine.GSO.4.10.10402252037310.18540-100000@brutus.uwaterloo.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Many thanks to all who pointed out the usefulness of the 'ANALYZE' command
in my last question. I assumed the db admin was doing 'VACUUM ANALYZE'
after each days insert but he wasn't.

Is there a shorthand notation when performing a multi-table join and one
column is to be equaled in all tables? i.e. the following gets tedious to
write:

select tq1.timestamp as t, tq1.value as q1, tq2.value as q2,
tq3.value as q3, tq4.value as q4 from
cal_quat_1 tq1, cal_quat_2 tq2, cal_quat_3 tq3, cal_quat_4 tq4
where tq1.timestamp = tq2.timestamp
and tq1.timestamp = tq3.timestamp
and tq1.timestamp = tq4.timestamp
and tq2.timestamp = tq3.timestamp
and tq2.timestamp = tq4.timestamp
and tq3.timestamp = tq4.timestamp
and tq1.timestamp > '2004-01-12 09:47:56.0000 +0'::timestamp with timezone
and tq1.timestamp < '2004-01-12 09:50:44.7187 +0'::timestamp with timezone
order by tq1.timestamp;

Each timestamp is indexed so the above is actually quite quick.
Nevertheless, the syntax of equating each table's timestamp to the others
is rather verbose.

Cheers,
Randall

Browse pgsql-general by date

  From Date Subject
Next Message Joe Conway 2004-02-26 02:07:32 Re: key = currval('tab_key_seq') choses SEQSCAN?!
Previous Message Eric B.Ridge 2004-02-26 01:32:30 Re: key = currval('tab_key_seq') choses SEQSCAN?!