Simplyfying many equals in a join
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
Is there a shorthand notation when performing a multi-table join and one
column is to be equaled in all tables?
Is this you are looking for?
SELECT t1.c7,t2.c6
FROM t1,t2
USING (c1,c2,c3)
WHERE t1.c4='2004-2-28' AND t2.c5='xyz'
performs the same as
SELECT t1.c7,t2.c6
FROM t1,t2
WHERE t1.c1=t2.c1 and t1.c2=t2.c2 and t1.c3=t2.c3
and t1.c4='2004-2-28' AND t2.c5='xyz'
CN
Import Notes
Resolved by subject fallback
Thanks... like so many other things, that seems obvious now.
On 25 Feb 2004, at 21:22, Jim Wilson wrote:
Show quoted text
Like this:
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 tq2.timestamp = tq3.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;The "and" makes anything more reduntant.
Best,
Jim Wilson
Import Notes
Reply to msg id not found: twig.1077762154.46540@kelcomaine.comReference msg id not found: twig.1077762154.46540@kelcomaine.com | Resolved by subject fallback
Is there a shorthand notation when performing a multi-table join and
What's the difference between a "multi-table join" and a "join"?
one column is to be equaled in all tables?
Is this you are looking for?
SELECT t1.c7,t2.c6
FROM t1,t2
USING (c1,c2,c3)
WHERE t1.c4='2004-2-28' AND t2.c5='xyz'performs the same as
SELECT t1.c7,t2.c6
FROM t1,t2
WHERE t1.c1=t2.c1 and t1.c2=t2.c2 and t1.c3=t2.c3
and t1.c4='2004-2-28' AND t2.c5='xyz'CN
I think this should work, too:
SELECT t1.c7,t2.c6
FROM t1,t2
WHERE (t1.c1, t1.c2, t1.c3, t1.c4, t2.c5)= (t2.c1, t2.c2, t2.c3,
'2004-2-28', 'xyz')
~Berend Tober