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

Reply via email to