Jump to content


Photo

Like operator


  • Please log in to reply
3 replies to this topic

#1 dug

dug
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 29 August 2006 - 04:11 PM

Hi folks,

is the below query correct? 

retrieve all courses in the past that have a name that starts with Cert


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


Thanks

dug

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 29 August 2006 - 04:29 PM

Looks fine to me... but you should have kept this post in the original thread.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 dug

dug
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 29 August 2006 - 04:36 PM

thanks fenway.  sorry for re-posting.  while you here on the initial post for this question:

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

you said i should subtract start and end date, is this what you meant?

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 – c.end_date
ORDER BY c.start_date ASC


#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 29 August 2006 - 05:49 PM

No... check out the TO_DAYS() function.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users