problem with mysql query on 2 tables

safra

Hi,

I have two tables: courses, courseRates

What I need is list all courses including their rates within a certain period. It is possible that some courses are queried more then once because there are several rates within the specific time span chosen. This is no problem.

The problem I am having is that I also want to display all courses for which the course rate is not yet available in the specific period.

a normal MySQL query like:

SELECT courses.name,courses.link,courses.region,courseRates.public,courseRates.descr 
FROM courses,courseRates WHERE ".implode(" AND ",$array)."
ORDER BY `courses.name` ASC

will only find those courses for which also course rates have been found.

I wonder what the easiest way is to show all courses (sorted) and include those courses that were not found with a 'not yet available' mark for the rates?

Hope this explanation of my problem was clear and that there is anyone who can help me getting started with this.

regards,

eoghain

What you want to do is called an outer join (I'm assming that there is no record in the courseRates table for the course. I'm also assuming that you have a field in courses called ID that holds a unique ID for every record in that table, and that you are placing that ID into a field called course_id in the courseRates table for every rate that this course could have.

SELECT courses.name,
       courses.link,
       courses.region,
       courseRates.public,
       courseRates.descr 
  FROM courses
  LEFT JOIN courseRates ON (courses.id = courseRates.course_id)
 WHERE ".implode(" AND ",$array)."
 ORDER BY `courses.name` ASC

There are alot of assumptions going on in the above query, so if this doesn't match your tables, or doesn't make sence I suggest that you post your table schemas and I'll take a look at them.

safra

Thanks eoghain!

You are right with your assumptions and will make the changes where necessary. Just not sure about your remark about those records in courses with no record in courseRates.

All records in courses have records in courseRates associated with them.

However, the WHERE statement may have date restrictions like:

WHERE courseRates.dateFrom >= "01-01-2004"

This may lead to NO results for some records in courses where the rate for this period has not yet been set. Nevertheless I do want to include those records in the results with a "not yet avalaible" mark.

Does your code work for this situation as well? Anyway I will try it and probably find out!

Thanks again

eoghain

Yes the sql I posted should work. What LEFT JOIN does is to take all rows from the left side of the join (courses in this case) and fill in all columns for the given right side table where there is a match, otherwise leave it null. Where an INNER JOIN would have only displayed the rows where there was a match on both sides of the join.

safra

Thanks again,

I got it working. But it seems there is no way to include search criteria for courseRates.

For those courses that have records in courseRates I like to filter them on the date and tried something like this in the mysql query for the WHERE clause:

((courseRates.dateFrom <= '$today' AND courseRates.dateTill >= '$today') OR courseRates.dateFrom = '' OR courseRates.dateFrom = '0000-00-00')

I added the last 2 conditions to match those courses that have no records in courseRates for the main date condition as my thought is that, through the table join, these fields are set to either '' or '0000-00-00'.

Nevertheless this seems to be ignored. The results of the above query is only those courses with records in courseRates that match the main date condition?

Any thoughts about this? can this be done in one MySQL query or should I use the date conditions afterwards in php code while parsing the MySQL results?

Thanks!

safra

Think I solved it by putting all conditions for the courseRates table inside the JOIN clause instead of the WHERE clause.

safra

The only thing I can't get to work at this stage is to order the results of table courseRates on the column 'dateFrom'.

ORDER BY 'courses.name','courseRates.dateFrom' ASC

seems to have no effect.

Any suggestions on this one?

Thanks!