Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2007
    Posts
    72

    Unanswered: Multiple (as in lots of) Joins

    I have a database with (in part) the following tables to record educational qualifications of staff:

    tblStaff_names
    tblProvbods (institution/university name)
    tblEd_qual (educational qualifications
    tblStaffedqual (details of staff members educational quals)
    tblProf_qual (professional memberships/quals)
    tblStaffprofqual (details of staff members professional quals)
    tblShortcourse (CPD and short courses)
    tblStaffShortcourse (details of shortcourses attended by staff).

    I can put together a query that uses tblStaff_names, tblEd_qual, tblProvbods, tblStaffedqual to show the names of staff and the educational qualifications they have and what institution it is from.

    Code:
    SELECT tblStaff_names.Surname, tblStaff_names.First_name, tblEd_qual.Ed_qual, tblStaff_edqual.staffidED, tblStaff_edqual.edqualid, tblStaff_edqual.EdQualLevel, tblStaff_edqual.provbodided, tblProv_bods.Providing_Body, tblStaff_names.Directorate, tblStaff_names.Team, tblStaff_names.Email, tblStaff_names.STAFFIDPK
    FROM tblStaff_names INNER JOIN (tblEd_qual INNER JOIN (tblProv_bods INNER JOIN tblStaff_edqual ON tblProv_bods.PROVBODIDPK = tblStaff_edqual.provbodided) ON tblEd_qual.EDQUALPK = tblStaff_edqual.edqualid) ON tblStaff_names.STAFFIDPK = tblStaff_edqual.staffidED;
    I can repeat the process to show professional qualifications and again to show shortcourses attended. So this gives me 3 queries.

    What I want is a query that combines all of these to display the staff members name and then all of their qualifications from tblStaffedqual, tblStaffprofqual and tblStaffShortcourse.
    Can anyone help?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im not surprised you have so many joins....
    it should be possible by a series of inner/left joins to expand you base query to return any or all qualifications a person has in a single query

    but In my books the design looks suspect
    what is the difference in data storage tersm between all the types of qualificiations
    ferinsatnce what differentiates staff professional and educational qualifications

    what differentiates normal educational qualificatiosn and staff qualification.

    in my books this design is going to cause headaches. IIRC at a previous contract they had members of staff who not only were teaching, they were also taking (studying) two separate courses.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2006
    Location
    New England
    Posts
    12
    I agree w/healdem I would look at the overall design of the database itself. To simplify the query I reworked it as this to get you started, but not sure if there will be any dups or missing fields when you look at the data. May need to run as data-definition. Just wanted to provode you w/a general outline.

    SELECT tblStaff_names.Surname,
    tblStaff_names.First_name,
    tblEd_qual.Ed_qual,
    tblStaff_edqual.staffidED,
    tblStaff_edqual.edqualid,
    tblStaff_edqual.EdQualLevel,
    tblStaff_edqual.provbodided,
    tblProv_bods.Providing_Body,
    tblStaff_names.Directorate,
    tblStaff_names.Team,
    tblStaff_names.Email,
    tblStaff_names.STAFFIDPK

    FROM tblStaff_names, tblEd_qual, tblProv_bods, tblStaff_edqual, tblStaff_edqual

    WHERE tblProv_bods.PROVBODIDPK = tblStaff_edqual.provbodided AND
    tblStaff_names.STAFFIDPK = tblStaff_edqual.staffidED AND
    tblEd_qual.EDQUALPK = tblStaff_edqual.edqualid;

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would get into the habit of writing ANSI joins. Apart from anything else, inner joins won't work if a person can have zero entries in one of the qual tables. You've also accidentally created a Cartesian product there (although the query will fail at syntax check before it does that).

    ++1 for one single table of quals.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2007
    Posts
    72
    Quote Originally Posted by healdem
    Im not surprised you have so many joins....
    it should be possible by a series of inner/left joins to expand you base query to return any or all qualifications a person has in a single query
    That's good I was thinking of left joins.

    Quote Originally Posted by healdem
    but In my books the design looks suspect
    what is the difference in data storage tersm between all the types of qualificiations
    ferinsatnce what differentiates staff professional and educational qualifications
    The difference in the qualifications isn't that much actually they are just different types of qualifications so they are in seperate tables so they are easily differentiated. I suppose this could be done with a separate field for type of course in one big qualifications table. The only other difference is that educational has a level field of (First degree, Masters, PhD etc) while Professional qualifications are (Associate, Fellow, Member etc).

    Quote Originally Posted by healdem
    what differentiates normal educational qualificatiosn and staff qualification.
    The tables with Staff in the name are a separate recording of the qualifications so Staff_edqual has the staffid number as a foreign key and then as many(or few) educational qualifications as each staff member has. The Staffprofqual and StaffShortcourse are the same. I was starting to think this was complicated but earlier I had other issues I was trying to solve

    Quote Originally Posted by healdem
    in my books this design is going to cause headaches. IIRC at a previous contract they had members of staff who not only were teaching, they were also taking (studying) two separate courses.
    Would a table like:
    tblQualifications
    staffid (foreign key to Staff_names.STAFFIDPK)
    Qualification name
    Providing Body ID (foreign key to tblProvBody)
    Type of Course (University/College, Professional, Shortcourse)
    Level of qualification (First Degree, Fellow, Member, Certificate etc)
    SubjectArea (foreign key to tblJASCsubjects)

    be a simpler solution, now that it's written this way I don't see why not.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes - perfect.

    You have another problem with your current design - because you have effectively recorded your quals horizontally, the number of rows your left join query would return would be something like n1 * n2 * n3 * n4 where each of the n#s is the number of each type of qual a person has.

    Go with your new design
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jul 2007
    Posts
    72
    Thanks very much for that (and thanks pootle flump)I'm now looking at one table for quals but I think I may need the help with the query syntax later. Oh I see Pootle Flump is saying that your sql is not ANSI. I will remember that.

  8. #8
    Join Date
    Jul 2007
    Posts
    72
    That first thanks was to Ottovan. Thanks healdem I think I get it now. I was seeing the faults come out but I was having trouble recognising them properly.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by bronisaurus
    I may need the help with the query syntax later.
    You might not have twigged it yet but for most queries your SQL will now be much simpler.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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