Am 02.03.2010 09:49, schrieb Carlos Konstanski: > 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
If the database behaves in a transactional way within the lifetime of a single SQL query, you could get away with this: SELECT * FROM msft WHERE date = (SELECT date FROM msft LIMIT 1) AND TIME = (SELECT time FROM msft LIMIT 1) UNION SELECT * FROM goog WHERE date = (SELECT date FROM msft LIMIT 1) AND TIME = (SELECT time FROM msft LIMIT 1) UNION SELECT * FROM appl WHERE date = (SELECT date FROM msft LIMIT 1) AND TIME = (SELECT time FROM msft LIMIT 1) UNION SELECT * FROM intc WHERE date = (SELECT date FROM msft LIMIT 1) AND TIME = (SELECT time FROM msft LIMIT 1) Carlos _______________________________________________ PLUG mailing list [email protected] http://lists.pdxlinux.org/mailman/listinfo/plug
