Re: Hopefully Stupid Question

Daniel Morgan (dmorgan_at_exesolutions.com) wrote:
: 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

Undoubtably correct for his original query, but I'm not sure I understand your point as I suggested that perhaps he was misunderstanding what he really wanted to do, and my alternate suggestions have nothing to do with joins at all.

I thought he might have a situation such as the following example, where different things are recorded by time and he wants to build a time-sequenced report that combines them all, but hadn't figured out the correct general structure of combinuing the data.

E.g.

  table 1 has "people entering building" 
  table 2 has "people logging onto the network"
  table 3 has "people making phone calls"

I theorize he wants a report that eventually looks like

	10:00 "joe enter building"
	10:05 "fred enters building"
	10:09 "joe logs in to network"
	10:11 "joe makes long distance phone call"

... ... ... Received on Tue Jun 04 2002 - 17:52:36 CDT