Sir, 99% of the time, if someone can't create a TEMPORARY table, it
means that they're using an earlier version of MySQL that doesn't
support temp tables. The best thing to do is to update to the most
recent version. If you can't update (e.g. if MySQL is being
maintained by a web hosting service), then you will have to create a
persistant table, and then delete it before ending your session. If
you're going to be using the table frequently, you can leave it in
your database and just empty it before or after each use.
Bob Hall
>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/
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
---------------------------------------------------------------------
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
