Thread: 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

Thanks... like so many other things, that seems obvious now.

On 25 Feb 2004, at 21:22, Jim Wilson wrote:

> 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

>>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