[PLUG] MSQL Cross-Table Query

[drew wymore]

On Tue, Mar 2, 2010 at 12:09 AM, D. Cooper Stevenson
<cooper at cooper.stevenson.name> 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
> PLUG at lists.pdxlinux.org
> 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-