Am 02.03.2010 01:09, schrieb D. Cooper Stevenson: > 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
Does mysql SQL support the UNION keyword? If so, you could run 2 queries. The first query SELECTs the date and time you want, and the second query SELECTs all the rows from all 4 tables with that date/time: SELECT date, time FROM msft LIMIT 1; SELECT * FROM msft WHERE date = :date AND TIME = :time UNION SELECT * FROM goog WHERE date = :date AND TIME = :time UNION SELECT * FROM appl WHERE date = :date AND TIME = :time UNION SELECT * FROM intc WHERE date = :date AND TIME = :time (replace :date and :time with the values you obtained in the first query) This is easily modified to query a date range: SELECT * FROM msft WHERE date BETWEEN :whatever1 AND :whatever2 UNION ...etc Your application logic can more easily calculate the date begin and end points. Mysql has crappy support for stored procedures. We could do it all in the database with a little PLSQL if you used Postgresql. Carlos _______________________________________________ PLUG mailing list [email protected] http://lists.pdxlinux.org/mailman/listinfo/plug
