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
