Mysql - Complex Join (ish)

[danIT]

I think I need a 'JOIN' but cant get it working, probably because I cant get my head round things....

Table1 - User details

+------------+
| ID | Name |
+------------+
| 2 | Dan |
--------------

Table2 - Course Assignment
+----------------+
| ID | CourseID |
+----------------+
| 2 | 100 |
| 2 | 104 |
------------------

Table3 - CourseDetails

+----------------------------+
| ID | CourseID |
+----------------------------+
| 2 | Year 10 Science |
| 2 | Year 10 Maths |
------------------------------

I want to select the user and find out which courses they are assigned to and finally output the name of the courses they are part of based on there assignment.

Select user courses
from Table1, Table2, Table3,

Help appreciated!

[powdarrmonkey]

Your tables should look like this:

Table1 - UserDetails
+-------------------+
| StudentID | Name  |
+-------------------+
| 2         | Dan   |
--------------------+

Table2 - CourseAssignment
+----------------------------------+
| AssignID | StudentID | CourseID  |
+----------------------------------+
| 1        | 2         | 100       |
| 2        | 2         | 104       |
-----------------------------------+

Table3 - CourseDetails 
+----------------------------------+
| CourseID | CourseDesc            |
+----------------------------------+
| 100      | Year 10 Science       |
| 104      | Year 10 Maths         |
-----------------------------------+

I've added a primary key to Table2, otherwise you can't use it for many-to-one relationships, and done what I think you intended for Table3. Giving the ID fields more useful names also makes it far easier when you come to build relationships. Same goes for table names without spaces in (as above).

Now you can find all the details about a course with this example query:

SELECT * FROM `CourseDetails` WHERE `CourseID`=100;

And the courses being taken by a student with the query:

SELECT `CourseID` FROM `CourseAssignment` WHERE `StudentID`=2;

Put the two together:

SELECT CourseDetails.* FROM CourseAssignment LEFT JOIN CourseDetails ON CourseAssignment.CourseID=CourseDetails.CourseID WHERE CourseAssignment.StudentID=2;

(disclaimer: I did this in my head, so I accept no responsibility if it DROPs your database without warning )

[danIT]

Wohoo, thanks for the advice and the query details....Success!

[lightinthedark]

I've added a primary key to Table2, otherwise you can't use it for many-to-one relationships

I don't think I agree with this. I have several tables in my database with the equivalent of his StudentId, CourseId to define many-to-many relationships. The 2 columns form a composite key without the need for an artificial primary key. Although this seems to be a question of personal taste I think it's misleading to say that you need an extra column to be the primary key.

This mentions the second thing I question about your response: isn't it a many-to-many relationship, not a many-to-one? Surely there is more than one pupil, and more than one class. There would be many pupils in many classes. Sorry to be picky, but if we're trying to educate people here I think it's important that we get the terms right.

If I'm wrong (which I don't think I am), please let me know so I can pass on this new learning to others.

Hello
:-Dave

[powdarrmonkey]

The 2 columns form a composite key without the need for an artificial primary key. Although this seems to be a question of personal taste I think it's misleading to say that you need an extra column to be the primary key.

To take another example (as I happen to be in front of a Unix machine): imagine a table of users, a table of processes, and an intermediate table indicating which users are executing what process. It's true to say that the same user could execute many instances of the same process, so to uniquely identify a running process an additional primary key is required (if, say, an administrator wanted to kill it).

Whilst it's not strictly necessary in danIT's table, I think being able to uniquely identify a row is a good habit to be in. It's much easier to build in during design than to add later on when it's actually needed, and the overhead of maintaining the index is pretty insignificant unless you're manipulating many, many thousands of rows at a time. (30 years ago, it wasn't, but I don't think it's a problem nowadays).

isn't it a many-to-many relationship, not a many-to-one? Surely there is more than one pupil, and more than one class. There would be many pupils in many classes. Sorry to be picky, but if we're trying to educate people here I think it's important that we get the terms right.

Yes, you caught me. I meant to type many-many.

[PaulBM]

I'm with lightinthedark too, I don't usually have unique ids on my many to many tables.
Although, powdarrmonkey also makes a valid point that it won't really make a lot of difference if you do add the column.

I have seen people use both fields as the primary key to trap duplicates. In theory a student should never be on the same course twice. Then again, your code should check that the student isn't on the course already before adding to the many to many table.