Re: Hopefully Stupid Question

spence wrote:

> Hi,
>
> Forgive me if I'm having a brain fart, but I can't seem to translate what I
> want to good SQL - I'm using Oracle 8.1.7. Here's the situation...
>
> I have say 3 tables and each of them has a character based timestamp (don't
> ask) with an identical fieldname, TS. I want to select various fields from
> each of the 3 tables and order the results based on the timestamp field they
> all have in common, TS. Perhaps something similar to:
>
> select
> table1.data1,
> table2.data2,
> table3.data3,
> table1.TS,
> table2.TS,
> table3.TS
> from
> table1, table2, table3
> order by TS;
>
> I realize that the above won't work because the TS in the order-by clause is
> ambiguous. How can I tell it to combine all the TSs and sort the rows based
> on that?
>
> Help!?!
>
> S

What you wrote won't work for a number of reasons. The most eggregious is the fact that you have a Cartesian Join.

Definitely spend some time learning about the WHERE clause.

But back to your question.

SELECT a.data1, b.data2, c.data3, a.ts
FROM table1 a, table2 b, table3 c
WHERE a.ts = b.ts
AND a.ts = c.ts
ORDER BY a.ts;

Daniel Morgan Received on Tue Jun 04 2002 - 14:42:29 CDT