Re: Hopefully Stupid Question

Malcolm Dew-Jones wrote:

> Daniel Morgan (dmorgan_at_exesolutions.com) wrote:
> : 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
>
> I suspect he means that the timestamp data is not identical in the three
> tables (just the column name).
>
> In that case do one of the following
>
> -1- all data, same colums of output
>
> select data1,TS from table1
> union all
> select data2,TS from table2
> union all
> select data3,TS from table3
> order by 2
> ;
>
> I can't check if my "order by" is in the right spot to order the whole
> union, so look it up to be sure.
>
> -2- all data, but different columns for output
>
> select data1,'','',TS from table1
> union all
> select '',data2,'',TS from table2
> union all
> select '','',data3,TS from table3
> order by 4
> ;
>
> (Same question about my placing of the order by).
>
> In case 2, you might be able to force the data from different columns but
> with the same timestamp into the same row by wrapping the above query in
> another query that does a group by on the TS to merge the rows from the
> same time. (use MAX to get the data for each column). However, that only
> works correctly if each timestamp is unique within each original table.
>
> i.e. something like
>
> select max(data1),max(data2),max(data3),TS
> from THE-ABOVE-2-
> group by TS
> order by TS
> ;
>
> MAX is not really taking the maximum here, it's actually just selecting
> the non-null data. Note caveat on whether the TS is unique in each
> original table for this to work correctly.

I would agree with you that it seems unlikely the three timestamps would be identical.

But if they are not the query becomes impossible.

Because every single record joins with every single record.

In which case a sequence should be created and used as a surrogate key.

Daniel Morgan Received on Tue Jun 04 2002 - 16:06:39 CDT