Jump to content


Photo

SQL: query help plz


  • Please log in to reply
10 replies to this topic

#1 dug

dug
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 29 August 2006 - 01:48 PM

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

#2 dug

dug
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 29 August 2006 - 02:29 PM

anyone plz?

:-[



#3 fenway

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

Posted 29 August 2006 - 02:37 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 dug

dug
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 29 August 2006 - 02:54 PM

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.


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

#5 fenway

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

Posted 29 August 2006 - 04:27 PM

You need to use "LIKE 'Cert%'"... for the dates, you can use the TO_DAYS() function.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 dug

dug
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 29 August 2006 - 05:19 PM

Hi fenway,

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

Cheers

Dug

#7 fenway

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

Posted 29 August 2006 - 05:51 PM

Returns the number of days... you can convert both, then subtract.  Alternatively, DATEDIFF() works as well, provided you have a recent version of MySQL.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#8 dug

dug
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 29 August 2006 - 06:07 PM

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

dug

#9 fenway

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

Posted 29 August 2006 - 06:55 PM

WHERE DATEDIFF( c.end_date, c.start_date ) > 1
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#10 dug

dug
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 29 August 2006 - 07:00 PM

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

cheers



#11 fenway

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

Posted 29 August 2006 - 11:29 PM

No, TO_DAYS() only take a single date.
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