Database Query

[obrienkev]

Hi all,

I have a many to many database schema.

I have the following tables: course, date_time, location, class.

The class table contains the foreign key of each of the other 3 tables.

I want to select all unique courses and related unique dates where course_id, date_time_id, and location_id are related in the class table.

course_id and date_time_id can have the same values but if so location_id must be different.

Make sense?

So I have a number of courses to be held in a few locations, and these courses could be held at the same time.

How do I write the query?
Here's what I've tried...

$query = "SELECT c.course_ID, c.course_name, d.date_ID, d.date_class ".
			 	 "FROM course c ".
				 "INNER JOIN class AS cl ".
				 "ON cl.course_ID = c.course_ID ".
				 "INNER JOIN date_time AS d ".
				 "ON cl.date_ID = d.date_ID ".
				 "INNER JOIN location AS l ".
				 "ON cl.location_ID = l.location_ID";

Thanks.

[Roger_Ramjet]

What I think you have is

class 1:M course
class 1:M date_time
class 1:M location

This means that each class relates to only 1 course, but each course relates to many classes, and the same for location and date_time.

That table should also have a multi-column Primary Key made up from the 3 Foriegn Key columns. Can't have 2 courses in the same location at the same time.

So the root table in your query must be 'class' not 'course'. Join the other 3 tables to class and you'll get the results you need.