[PLUG] MSQL Cross-Table Query

[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
> PLUG at lists.pdxlinux.org
> 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