[PLUG] MSQL Cross-Table Query

Carlos Konstanski ckonstanski at pippiandcarlos.com
Tue Mar 2 16:53:42 UTC 2010


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
>> 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
> _______________________________________________
> PLUG mailing list
> PLUG at lists.pdxlinux.org
> http://lists.pdxlinux.org/mailman/listinfo/plug

I wouldn't use the * in the query, BTW. I just did that to save typing.
Always explicitly specify the column names, especially in a UNION query. To
use UNION, the column names must match in all the SELECT statements.

Carlos



More information about the PLUG mailing list