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
