select m.close, a.close, g.close, i.close from msft m, aapl a, goog g, intc i where date = "$date" and time = "$time"
I don't think this works because date and time are ambiguous; they could be from any of the 4 tables. When you write a join without join criteria the transitive closure of all rows are generated and then the selection criteria applied. This can result in long query times. I suggest something of the form select "msft", close from msft where date = "$date" and time = "$time" union select "goog", close from goog where date = "$date" and time = "$time" union .. (the other two tables) Disclaimer: These results are the to the best of my recollection. I did not test them anywhere. -- Pat On Tue, Mar 2, 2010 at 12:20 AM, drew wymore <[email protected]> wrote: > On Tue, Mar 2, 2010 at 12:09 AM, D. Cooper Stevenson > <[email protected]> wrote: > > Hello Everyone, > > > > I've worked through the documentation but haven't yet worked out a > > cross-table query in MYSQL yielding the correct results. My goal is to > > pull data from four tables that correspond to the date-time stamp of the > > first table. This will make sense as I describe what I am trying to pull > > from the database below. > > > > I have three tables entitled, "msft," "goog," "aapl," and "intc." Each > > of these tables are in the same database entitled, "minute." > > > > Here is an example of each (they're essentially the same): > > > > msft: > > > > date time open high low close > volume > > | 2009-01-15 | 16:57:00 | 18.64 | 18.67 | 18.62 | 18.64 | 190394 | > > | 2009-01-15 | 16:58:00 | 18.63 | 18.63 | 18.61 | 18.62 | 60652 | > > | 2009-01-15 | 16:59:00 | 18.62 | 18.63 | 18.6 | 18.61 | 404419 | > > > > > > goog: > > > > date time open high low close > volume > > | 2009-01-15 | 16:49:00 | 299.32 | 299.32 | 299.32 | 299.32 | 550 | > > | 2009-01-15 | 16:51:00 | 299.93 | 299.93 | 299.93 | 299.93 | 500 | > > | 2009-01-15 | 16:55:00 | 299.4 | 299.4 | 299.39 | 299.39 | 269 | > > > > > > aapl: > > > > date time open high low close > volume > > | 2009-01-15 | 16:57:00 | 83.49 | 83.49 | 83.49 | 83.49 | 400 | > > | 2009-01-15 | 16:58:00 | 83.49 | 83.49 | 83.49 | 83.49 | 350 | > > | 2009-01-15 | 16:59:00 | 83.43 | 83.46 | 83.41 | 83.42 | 2290 | > > > > > > intc: > > > > date time open high low close > volume > > | 2009-01-15 | 16:57:00 | 13.57 | 13.58 | 13.56 | 13.56 | 1400 | > > | 2009-01-15 | 16:58:00 | 13.57 | 13.58 | 13.57 | 13.58 | 7517 | > > | 2009-01-15 | 16:59:00 | 13.58 | 13.58 | 13.56 | 13.58 | 2950 | > > > > What I would like to do is this: for each date & time combination for > > table 'msft' include the close column for msft, goog, aapl, and intc. > > > > An example would look like this: > > date time close close close close > > | 2009-01-15 | 16:57:00 | 18.64 | 299.32 | 83.49 | 13.56 | > > > > Bonus points for doing this within a specific date range. > > > > Thanks in advance for any help you may be able to provide. > > > > > > Very Best, > > > > > > Cooper Stevenson > > -- > > ph: 541.971.0366 > > em: cooper at cooper dot stevenson dot name > > www: http://cooper.stevenson.name > > > > _______________________________________________ > > PLUG mailing list > > [email protected] > > http://lists.pdxlinux.org/mailman/listinfo/plug > > > > something like > > select m.close, a.close, g.close, i.close from msft m, aapl a, goog g, > intc i where date = "$date" and time = "$time" > > I haven't tested this query, just off the top of my head. See if it > works and pulls out the date you're looking for and formats it > properly. > > Drew- > _______________________________________________ > PLUG mailing list > [email protected] > http://lists.pdxlinux.org/mailman/listinfo/plug > -- [email protected] www.timlick.com 503-476-3119 10990 NE Paren Springs Rd. Dundee OR 97115 _______________________________________________ PLUG mailing list [email protected] http://lists.pdxlinux.org/mailman/listinfo/plug
