Hi:
the way I handle this situation is:
"create temporary table if not exists tblName (somefld I need, etc.) select
iptable.* (or field names) from myTable where bluemoon = yes"
Works every time for me. You need to know that temporary tables are only
available to the creating user during the session in which it is created
(you can't find it with freemascon or any other tool that I am aware of)
after the session is completed the temporary table is poofware.
hope this helps.
Jack :-)=
----- Original Message -----
From: "Web Depressed" <[EMAIL PROTECTED]>
To: "Bob Hall" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, February 14, 2001 4:46 PM
Subject: Re: Can anyone do this ?
> Hi Bob/ All,
>
> I cannot get the creation of the TEMPORARY Table to
> work:
> The select statement works fine:
> mysql> SELECT table1_id, Count(date) as c FROM Table2,
> Table1
> -> WHERE Table2.date BETWEEN "2001-02-02" AND
> "2001-02-06"
> -> AND Table2.table1_id=Table1.id
> -> GROUP BY Table1.id HAVING c=3;
> +-----------+---+
> | table1_id | c |
> +-----------+---+
> | 1 | 3 |
> +-----------+---+
> 1 row in set (0.02 sec)
>
> mysql> CREATE TEMPORARY TABLE counts
> -> SELECT table1_id, Count(date) as c FROM Table2,
> Table1
> -> WHERE Table2.date BETWEEN "2001-02-02" AND
> "2001-02-06"
> -> AND Table2.table1_id=Table1.id
> -> GROUP BY Table1.id HAVING c=3;
>
>
> If I manually create this counts table and work with
> that I do get the desired output:
> mysql> CREATE TABLE count (table1_id VARCHAR(10), c
> VARCHAR(10));
> Query OK, 0 rows affected (0.06 sec)
>
> mysql> insert into count VALUES (1,3);
> Query OK, 1 row affected (0.01 sec)
>
> mysql> select * from count;
> +-----------+------+
> | table1_id | c |
> +-----------+------+
> | 1 | 3 |
> +-----------+------+
> 1 row in set (0.00 sec)
>
> mysql> SELECT Table1.*, Table2.*
> -> FROM Table1, Table2, count
> -> WHERE Table1.id=Table2.table1_id AND Table1.id
> = count.table1_id
> -> AND Table2.date BETWEEN "2001-02-04" AND
> "2001-02-06";
> +----+-------+------------+-------------+-----------+
> | id | Item | date | no_of_items | table1_id |
> +----+-------+------------+-------------+-----------+
> | 1 | Item1 | 2001-02-04 | 2 | 1 |
> | 1 | Item1 | 2001-02-05 | 2 | 1 |
> | 1 | Item1 | 2001-02-06 | 2 | 1 |
> +----+-------+------------+-------------+-----------+
> 3 rows in set (0.02 sec)
>
>
> Do you have any idea where I'm going wrong ?
>
> Kind Regards,
> -- Frank
>
> > Sir, create a variable day_count with the count of
> > days in the
> > user-supplied range. Then set up the following temp
> > table.
> >
> > CREATE TEMPORARY TABLE counts
> > SELECT table1_id, Count(DISTINCT date_field) as
> > cnt
> > WHERE data_field BETWEEN min AND max
> > GROUP BY id
> > HAVING cnt = day_count;
> >
> > Now you have a table with the IDs of items that
> > occur at least once
> > each day in the user-supplied range. You don't need
> > the DISTINCT if
> > each item can only have one record per day. Now some
> > inner joins
> > should get the results you want.
> >
> > SELECT table1.*, table2.*
> > FROM table1, table2, counts
> > WHERE table1.id = table2.table1_id AND table1.id
> > = counts.table1_id
> > AND date_field BETWEEN min AND max;
> >
> > I haven't actually run this, which means that
> > there's probably a
> > mistake or three.
> >
> > Bob Hall
>
>
> __________________________________________________
> Do You Yahoo!?
> Get personalized email addresses from Yahoo! Mail - only $35
> a year! http://personal.mail.yahoo.com/
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
