Am 02.03.2010 20:14, schrieb D. Cooper Stevenson: > On 03/02/10 13:13, Tim Garton wrote: > [snip] >> select m.`date`, m.`time`, m.`close`, g.`close`, a.`close`, i.`close` >> from msft m >> join goog g on g.`date` = m.`date` and g.`time` = m.`time` >> join aapl a on a.`date` = m.`date` and a.`time` = m.`time` >> join intc i on i.`date` = m.`date` and i.`time` = m.`time` >> where m.`date` between '2010-02-28' and '2010-03-02' >> > > Tim, you nailed it. You're query worked flawlessly. Have a look: > > date time close close close close > 2009-01-05 09:14:00 20.2 319.49 93.62 15.05 > 2009-01-05 09:26:00 20.15 320 93.28 15.07 > 2009-01-05 09:28:00 20.19 320 93.16 15.08 > > That's just good information. > > Moreover, I've started asking myself, "why does this work," and from > other responses, "what really is the deal with UNIONs and JOINs?" > > I am now also thinking of the steps necessary to re-engineer my database > based on Tim's suggestion. > > Carlos, Konstanski, too, offered a few "fleshed out" solutions--you can > tell he know what he's doing and gave me pause for thought. > > I'm impressed with the way you guys "locked on" to the problem and ran > with it. > > Thanks very much to all of you for your insightful answers, suggestions, > and references I can turn to for more information. I am grateful as late > last night (early this morning?) it appeared as I was looking at having > to climb a mountain. > > If you guys want a prediction for next week's MSFT stock, let me know. ;) > > > Very Truly Yours, > > > Cooper Stevenson > -- > ph: 541.971.0366 > em: [email protected] <mailto:[email protected]> > www: http://cooper.stevenson.name > _______________________________________________ > PLUG mailing list > [email protected] > http://lists.pdxlinux.org/mailman/listinfo/plug
All in a day's work. There's a lot to be said for learning the fundamentals; then you can solve problems all day long. In SQL, this means getting a solid grip on joins, subselects, and transactions, among other things. Naturally each database engine has its own syntactical quirks. Fancy stuff like connectby() can wait till later. Be careful with joins. They have a lot of overhead. A faulty join can do bad things to your database, like consuming vast amounts of temp space (which cannot always be reclaimed, at least in Oracle) and taking forever to run. Keep them as small and simple as possible. Every Oracle PLSQL developer has hosed a database at least once in his career with a bad join. If you have a join that takes a lot of time to run, you might want to consider running it on a schedule, and storing the results in a single table. Querying the same data from the single table requires far less effort, and runs far more quickly. But it won't be real time. I'm not suggesting this technique for this particular problem; it's one of those fundamentals to have in your toolbox. Carlos _______________________________________________ PLUG mailing list [email protected] http://lists.pdxlinux.org/mailman/listinfo/plug
