+ Post New Thread
Results 1 to 6 of 6
Coding Thread, Mysql - Complex Join (ish) in Coding and Web Development; I think I need a 'JOIN' but cant get it working, probably because I cant get my head round things.... ...
  1. #1

    Join Date
    Apr 2006
    Location
    UK
    Posts
    939
    Thank Post
    39
    Thanked 70 Times in 54 Posts
    Rep Power
    29

    Mysql - Complex Join (ish)

    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!

  2. #2

    powdarrmonkey's Avatar
    Join Date
    Feb 2008
    Location
    Alcester, Warwickshire
    Posts
    4,859
    Thank Post
    412
    Thanked 777 Times in 650 Posts
    Rep Power
    182
    Your tables should look like this:

    Code:
    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:

    Code:
    SELECT * FROM `CourseDetails` WHERE `CourseID`=100;
    And the courses being taken by a student with the query:

    Code:
    SELECT `CourseID` FROM `CourseAssignment` WHERE `StudentID`=2;
    Put the two together:

    Code:
    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 )
    Last edited by powdarrmonkey; 8th February 2009 at 07:26 PM.

  3. #3

    Join Date
    Apr 2006
    Location
    UK
    Posts
    939
    Thank Post
    39
    Thanked 70 Times in 54 Posts
    Rep Power
    29
    Wohoo, thanks for the advice and the query details....Success!

  4. #4

    Join Date
    Oct 2008
    Location
    Gosport
    Posts
    64
    Thank Post
    1
    Thanked 18 Times in 13 Posts
    Rep Power
    15
    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

  5. #5

    powdarrmonkey's Avatar
    Join Date
    Feb 2008
    Location
    Alcester, Warwickshire
    Posts
    4,859
    Thank Post
    412
    Thanked 777 Times in 650 Posts
    Rep Power
    182
    Quote Originally Posted by lightinthedark View Post
    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.

  6. #6

    Join Date
    Jan 2007
    Location
    Lowestoft, Suffolk
    Posts
    84
    Thank Post
    6
    Thanked 4 Times in 4 Posts
    Rep Power
    16
    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.

SHARE:
+ Post New Thread

Similar Threads

  1. vbs complex run commands
    By djones in forum Scripts
    Replies: 19
    Last Post: 18th December 2008, 10:55 PM
  2. Me + The Sims = God complex!
    By Little-Miss in forum Gaming
    Replies: 9
    Last Post: 10th November 2008, 04:23 PM
  3. Replies: 1
    Last Post: 7th December 2007, 07:58 PM
  4. Site Live-ish
    By bensewell in forum Web Development
    Replies: 32
    Last Post: 5th October 2007, 08:55 PM
  5. Replies: 1
    Last Post: 27th August 2006, 08:34 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •