Thread: Two Inner Joins

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2000
    Location
    Bookham, Surrey, UK
    Posts
    4
    Rep Power
    0
    Hi

    Is it possible to run a query with two innerjoins on MySQL, such as

    SELECT DISTINCT qualification,exambody
    FROM Qualifications LEFT JOIN (ExamBodies LEFT JOIN ExamDates ON ExamBodies.ExambodyID = ExamDates.exambodyID)
    ON Qualifications.QualificationID = ExamDates.qualificationID
    ORDER BY qualification, exambody

    The above works fine in SQL Server 7 and Access but I can't find a solution to getting the distinct examining body and qualifications from the ExamDates table.

    Any help much appreciated. This query will be run very frequently so creating temperorary tables is not an option..

    Mike
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Well, you haven't stated what table qualification and exambody are on as you will need to specify if either field name appears in more than one table. With that in mind, this should work:

    select distinct qualification,distinct exambody from qualifictions q,exambodies eb, examdates ed where q.QualificationID = ed.qualificationID and eb.ExambodyID=ed.exambodyID order by qualification,exambody

  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2000
    Location
    Bookham, Surrey, UK
    Posts
    4
    Rep Power
    0
    I'm getting a parse error near 'distinct exambody from qualifictions'

    If I take out the second distinct the query will run but I then get 'The table SQLbb5_0 is full' The table Examdates had 4500 records in it and the query should return 21 distince rows. Any further clues

    Thanks Mike

Similar Threads

  1. Should I relate tables using joins, or not?
    By lucretius in forum Development Articles
    Replies: 0
    Last Post: November 25th, 2003, 12:37 AM
  2. Joins Again
    By Rhys in forum MySQL Help
    Replies: 1
    Last Post: September 7th, 2003, 04:54 AM
  3. nested inner joins
    By gaagaagui in forum MySQL Help
    Replies: 1
    Last Post: August 16th, 2002, 07:25 AM
  4. Outer joins
    By wolfespawn in forum PostgreSQL Help
    Replies: 4
    Last Post: July 29th, 2002, 03:26 PM
  5. tutorials on joins?
    By strider3700 in forum MySQL Help
    Replies: 1
    Last Post: June 27th, 2000, 07:40 AM

IMN logo majestic logo threadwatch logo seochat tools logo