[PLUG] MSQL Cross-Table Query

[Tim Garton]

If you know for sure that you are going to have data for every minute
and never duplicate data, and that the `time` column will always be 0
seconds, something like:

select m.`date`, m.`time`, m.`close`, g.`close`, a.`close`, i.`close`
from msft m
join goog g on g.`date` = m.`date` and g.`time` = m.`time`
join aapl a on a.`date` = m.`date` and a.`time` = m.`time`
join intc i on i.`date` = m.`date` and i.`time` = m.`time`
where m.`date` between '2010-02-28' and '2010-03-02'

you may want to look at combining the date and time columns into one
column, with a type of datetime or timestamp.  May also want to look
at normalizing the data, either putting it all into one table or else
something like:

entry:
| id | date | time |

msft:
| entry_id | open | high | low | close | volume |

goog:
| entry_id | open | high | low | close | volume |

aapl:
| entry_id | open | high | low | close | volume |

intc:
| entry_id | open | high | low | close | volume |


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
>