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.
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 conditionT LEFT JOIN U ONcondition holds rows where (for U-only columns U1,...)T WHEREcondition holds rows where T(...) AND conditionT 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 whereVALUES (C1, C2, ...)((v1,v2, ...), ...) holds rows whereAlso:
(...) IN T means T(...)scalar= T means T(scalar)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.)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments