SQL: query help plz

[dug]

Hi all,

Can someone care to please help me answer the questions below for this query?

SELECT c.ID AS 'Course ID',
date_format(c.start_date,'%d/%m/%Y') AS 'Start Date',
date_format(c.end_date,'%d/%m/%Y') AS 'End Date' ,
t.description AS 'Course Description',

FROM media_courses AS c
INNER JOIN media_courses_type AS t
ON t.ID = c.media_courses_type_ID

WHERE c.start_date > NOW()
GROUP BY c.ID
ORDER BY c.start_date ASC

what does the query above would return?

how do i Re-write the query to retrieve all courses in the past that a name that starts with Cert

how do i Re-write the query to return all courses that are more than 1 day in length



Cheers

Dug

[dug]

anyone plz?

:-[

[fenway]

Currently, it's returning all future courses... though I don't see the need for a GROUP BY, since it's an INNER JOIN, and will only return one record per media_course record anyway.  You should be able to change the where clause easily to check the "past" and examine the course title; for duration, simply subtract the start & end dates.

[dug]

[quote author=fenway link=topic=106082.msg424019#msg424019 date=1156862276]
Currently, it's returning all future courses... though I don't see the need for a GROUP BY, since it's an INNER JOIN, and will only return one record per media_course record anyway.  You should be able to change the where clause easily to check the "past" and examine the course title; for duration, simply subtract the start & end dates.
[/quote]

Hi fanway,

thanks for the help.  as for the second question is this what you had in mind:
SELECT c.ID AS 'Course ID',
date_format(c.start_date,'%d/%m/%Y') AS 'Start Date',
date_format(c.end_date,'%d/%m/%Y') AS 'End Date' ,
t.description AS 'Course Description',

FROM media_courses AS c
INNER JOIN media_courses_type AS t
ON t.ID = c.media_courses_type_ID

WHERE c.end_date < NOW()
AND t.description LIKE 'Cert'
ORDER BY c.start_date ASC

and as for substracting the the dates how do i do that?  Can you plz care for a reply?

thanks

dug

[fenway]

You need to use "LIKE 'Cert%'"... for the dates, you can use the TO_DAYS() function.

[dug]

Hi fenway,

the TO_Days () function what exatly does it do?  Tired looking it up on mysql.com with no success.

Cheers

Dug

[fenway]

Returns the number of days... you can convert both, then subtract.  Alternatively, DATEDIFF() works as well, provided you have a recent version of MySQL.

[dug]

fenway could you care for a demo how to use DATEDIF() with my query above plz?

dug

[fenway]

WHERE DATEDIFF( c.end_date, c.start_date ) > 1

[dug]

thanks fenway.  Would the syntax be the same for TO_Days () (i.e. WHERE TO_Days (c.start_date - c.emd_date)?

cheers

[fenway]

No, TO_DAYS() only take a single date.