[PLUG] MSQL Cross-Table Query

[Patrick J. Timlick]

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 don't think this works because date and time are ambiguous; they could be
from any of the 4 tables.

When you write a join without join criteria the transitive closure of all
rows are generated and then the selection criteria applied.  This can result
in long query times.

I suggest something of the form

select "msft", close from msft  where date = "$date" and time = "$time"
union
select "goog", close from goog  where date = "$date" and time = "$time"
union
.. (the other two tables)

Disclaimer: These results are the to the best of my recollection.  I did not
test them anywhere.

-- Pat

On Tue, Mar 2, 2010 at 12:20 AM, drew wymore <drew.wymore at gmail.com> wrote:

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



-- 
p.j.timlick at ieee.org
www.timlick.com
503-476-3119
10990 NE Paren Springs Rd.
Dundee OR 97115