By Levi Tonet


2014-12-29 01:02:13 8 Comments

I've got two tables in MS Access that keep track of class facilitators and the classes they facilitate. The two tables are structured as follows:

tbl_facilitators

facilID -> a unique autonumber to keep track of individual teachers
facilLname -> the Last name of the facilitator
facilFname -> the First name of the facilitator

tbl_facilitatorClasses

classID -> a unique autonumber to keep track of individual classes
className -> the name of the class (science, math, etc)
primeFacil -> the facilID from the first table of a teacher who is primary facilitator
secondFacil -> the facilID  from the first table of another teacher who is backup facilitator

I cannot figure out how to write an Inner Join that pulls up the results in this format:

Column 1:  Class Name
Column 2:  Primary Facilitator's Last Name
Column 3:  Primary Facilitator's First Name
Column 4:  Secondary Facilitator's Last Name
Column 5:  Secondary Facilitator's First Name

I am able to pull up and get the correct results if I only request the primary facilitator by itself or only request the secondary facilitator by itself. I cannot get them both to work out, though.

This is my working Inner Join:

SELECT tbl_facilitatorClasses.className,
    tbl_facilitators.facilLname, tbl_facilitators.facilFname
FROM tbl_facilitatorClasses
INNER JOIN tbl_facilitators
ON tbl_facilitatorClasses.primeFacil = tbl_facilitators.facilID;

Out of desperation I also tried a Union, but it didn't work out as I had hoped. Your help is greatly appreciated. I'm really struggling to make any progress at this point. I don't often work with SQL.

SOLUTION

Thanks to @philipxy I came up with the following query which ended up working:

SELECT tblCLS.className,
    tblP.facilLname, tblP.facilFname, tblS.facilLname, tblS.facilFname
FROM (tbl_facilitatorClasses AS tblCLS
INNER JOIN tbl_facilitators AS tblP
ON tblCLS.primeFacil=tblP.facilID)
INNER JOIN tbl_facilitators AS tblS
ON tblCLS.secondFacil=tblS.facilID;

When performing multiple Inner Joins in MS Access, parenthesis are needed...As described in this other post.

3 comments

@philipxy 2014-12-29 02:32:49

Every base table has a statement template, aka predicate, parameterized by column names, by which we put a row in or leave it out. We can use a shorthand for the predicate that is like its SQL declaration.

// facilitator [facilID] is named [facilFname] [facilLname]
facilitator(facilID, facilLname, facilFname)
// class [classID] named [className] has prime [primeFacil] & backup [secondFacil]
class(classID, className, primeFacil, secondFacil)

Plugging a row into a predicate gives a statement aka proposition. The rows that make a true proposition go in a table and the rows that make a false proposition stay out. (So a table states the proposition of each present row and states NOT the proposition of each absent row.)

// facilitator f1 is named Jane Doe
facilitator(f1, 'Jane', 'Doe')
// class c1 named CSC101 has prime f1 & backup f8
class(c1, 'CSC101', f1, f8)

But every table expression value has a predicate per its expression. SQL is designed so that if tables T and U hold the (NULL-free non-duplicate) rows where T(...) and U(...) (respectively) then:

  • T CROSS JOIN U holds rows where T(...) AND U(...)
  • T INNER JOIN U ONcondition holds rows where T(...) AND U(...) AND condition
  • T LEFT JOIN U ONcondition holds rows where (for U-only columns U1,...)
        T(...) AND U(...) AND condition
    OR T(...)
        AND NOT there EXISTS values for U1,... where [U(...) AND condition]
        AND U1 IS NULL AND ...
  • T WHEREcondition holds rows where T(...) AND condition
  • T INTERSECT U holds rows where T(...) AND U(...)
  • T UNION U holds rows where T(...) OR U(...)
  • T EXCEPT U holds rows where T(...) AND NOT U(...)
  • SELECT DISTINCT * FROM T holds rows where T(...)
  • SELECT DISTINCTcolumns to keepFROM T holds rows where
    there EXISTS values for columns to drop where T(...)
  • VALUES (C1, C2, ...)((v1,v2, ...), ...) holds rows where
    C1 = v1 AND C2 = v2 AND ... OR ...

Also:

  • (...) IN T means T(...)
  • scalar= T means T(scalar)
  • T(..., X, ...) AND X = Y means T(..., Y, ...) AND X = Y

So to query we find a way of phrasing the predicate for the rows that we want in natural language using base table predicates, then in shorthand using base table predicates, then in SQL using base table names (plus conditions wherever needed). If we need to mention a table twice then we give it aliases.

// natural language
there EXISTS values for classID, primeFacil & secondFacil where
    class [classID] named [className] has prime [primeFacil] & backup [secondFacil]
AND facilitator [primeFacil] is named [pf.facilFname] [pf.facilLname]
AND facilitator [secondFacil] is named [sf.facilFname] [sf.facilLname]

// shorthand
there EXISTS values for classID, primeFacil & secondFacil where
    class(classID,className, primeFacil, secondFacil)
AND facilitator(pf.facilID, pf.facilLname, pf.facilFname)
AND pf.facilID = primeFacil
AND facilitator(sf.facilID, sf.facilLname, sf.facilFname)
AND sf.facilID = secondFacil

// table names & (MS Access) SQL
SELECT className, pf.facilLname, pf.facilFname, sf.facilLname, sf.facilFname
FROM (class JOIN facilitator AS pf ON pf.facilID = primeFacil)
JOIN facilitator AS sf ON sf.facilID = secondFacil

OUTER JOIN would be used when a class doesn't always have both facilitators or something doesn't always have all names. (Ie if a column can be NULL.) But you haven't given the specific predicates for your base table and query or the business rules about when things might be NULL so I have assumed no NULLs.

(Re MS Access JOIN parentheses see this from SO and this from MS.)

@Levi Tonet 2014-12-29 02:49:40

Thank you so much. This was the solution I needed. I mirrored your SELECT statement and came up with a query that worked. I cannot thank you enough.

@Iwantallthedata 2014-12-29 01:30:29

I would do it as above by joining to the tbl_facilitators table twice but you might want to make sure that every class really does require a 2nd facilitator as the second join should be an outer join instead. Indeed it might be safer to assume that it's not a required field.

@Levi Tonet 2014-12-29 01:42:27

Unfortunately, a backup/secondary facilitator will always be required in this situation. I'm going to follow your suggestion and look into outer joins.

@philipxy 2014-12-29 02:15:10

@LeviTonet Outer join(s) are if a class doesn't always have both facilitators or something doesn't always have all names. (Ie if a column can be NULL.)

@wvdz 2014-12-29 01:08:37

Just do an extra join for the secondary facilitator (and please use table aliases!):

SELECT fc.className, f1.facilLname, f2.facilFname
FROM tbl_facilitatorClasses fc
INNER JOIN tbl_facilitators f1 ON fc.primeFacil = f1.facilID
INNER JOIN tbl_facilitators f2 ON fc.secondFacil = f2.facilID;

@Gordon Linoff 2014-12-29 01:22:42

This is correct in spirit but it is not MS Access syntax. You need as for the table aliases and parentheses around the joins.

@Levi Tonet 2014-12-29 01:44:01

Attempted your second inner join but received a syntax error (missing operator) when I tried it out. Sorry about not using aliases. I'll keep that in mind for the future.

Related Questions

Sponsored Content

19 Answered Questions

[SOLVED] How can I get column names from a table in SQL Server?

22 Answered Questions

[SOLVED] SQL update from one Table to another based on a ID match

18 Answered Questions

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

12 Answered Questions

[SOLVED] SQL Inner-join with 3 tables?

  • 2012-04-17 16:45:29
  • Bob Sanders
  • 1343890 View
  • 332 Score
  • 12 Answer
  • Tags:   sql join inner-join

1 Answered Questions

[SOLVED] SQL- Creating an Inner JOIN for Two Columns inside Same Table

2 Answered Questions

[SOLVED] Inner Join Help - How to order table join?

  • 2014-05-05 02:10:14
  • teep
  • 56 View
  • 1 Score
  • 2 Answer
  • Tags:   sql

4 Answered Questions

[SOLVED] SQL inner join on specified columns

  • 2013-09-11 13:21:14
  • Vera Gavriel
  • 84 View
  • 2 Score
  • 4 Answer
  • Tags:   sql join

1 Answered Questions

1 Answered Questions

[SOLVED] SQL INNER JOIN tables syntax

Sponsored Content