I don't know if i understood you very well, but here's a try..
mysql> select * from Classes;
+----+---------+
| ID | Name |
+----+---------+
| 1 | XO-312 |
| 2 | PA-211a |
| 3 | XUL-001 |
+----+---------+
3 rows in set (0.00 sec)
mysql> select * from Workshops order by ClassID,Date;
+----+---------+------------+
| ID | ClassID | Date |
+----+---------+------------+
| 1 | 1 | 2002-05-15 |
| 8 | 1 | 2002-09-22 |
| 7 | 1 | 2002-10-29 |
| 2 | 1 | 2003-02-20 |
| 3 | 2 | 2002-05-15 |
| 9 | 2 | 2003-01-01 |
| 4 | 2 | 2003-02-17 |
| 5 | 3 | 2002-05-15 |
| 10 | 3 | 2002-12-16 |
| 6 | 3 | 2003-01-01 |
+----+---------+------------+
10 rows in set (0.00 sec)
mysql> select ClassID, MIN(Date) min, MAX(Date) max, Classes.Name
-> FROM Workshops LEFT JOIN Classes ON (ClassID=Classes.ID)
-> GROUP BY ClassID HAVING now() BETWEEN min and max;
+---------+------------+------------+---------+
| ClassID | min | max | Name |
+---------+------------+------------+---------+
| 1 | 2002-05-15 | 2003-02-20 | XO-312 |
| 2 | 2002-05-15 | 2003-02-17 | PA-211a |
+---------+------------+------------+---------+
2 rows in set (0.00 sec)
Hope this helps...
On Sat, 2003-01-11 at 16:25, Steve Lefevre wrote:
> First of, thanks to all who replied to my questions earlier!
>
> Now I have another problem. I have a table of Classes and Workshops. Each
> Class has a number of workshops. Each workshop has a date.
>
> I have a query that gives me the date range of a class - the min and max
> dates of its workshops.
>
> "select ClassID, MIN(Date), MAX(Date), Classes.Name FROM Workshops LEFT JOIN
> Classes ON ClassID=Classes.ID GROUP BY ClassID;"
>
> gives me:
>
> +---------+------------+------------+---------+
> | ClassID | MIN(Date) | MAX(Date) | Name |
> +---------+------------+------------+---------+
> | 56 | 2002-05-15 | 2002-12-29 | XO-312 |
> | 408 | 2002-05-15 | 2002-05-17 | PA-211a |
> | 600 | 2002-05-15 | 2002-05-16 | XUL-001 |
> +---------+------------+------------+---------+
> 3 rows in set (0.00 sec)
>
> Now I want to get *active* classes - WHERE Now() Between MIN(Date) and
> Max(Date) -- but I can't figure out where to put the friggin clause. I get
> errors all over the place. Can I use the between function with a group by
> function?
>
> select ClassID, MIN(Date), MAX(Date), Classes.Name
> FROM Workshops
> LEFT JOIN Classes ON ClassID=Classes.ID
> WHERE Now() BETWEEN MIN(Date) and MAX(Date)
> GROUP BY ClassID;
>
> What am I doing wrong?
>
>
>
> ---------------------------------------------------------------------
> 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
>
--
Diana Soares
---------------------------------------------------------------------
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
