Join on multiple booleans

Question

I have two tables, each with three boolean (ms-access "Yes/No") columns.

Table 1: A1, B1, C1

Table 2: A2, B2, C2

I would like the rows from Table 2 given a specific row from Table 1 that satisfy the following conditions:

If A1 is true then only rows where A2 is true, if A1 is false then rows where A2 is true or false. If B1 is true then only rows where B2 is true, if B1 is false then rows where B2 is true or false. If C1 is true then only rows where C2 is true, if C1 is false then rows where C2 is true or false.

Example One

         A, B, C    
Table 1: 0, 1, 0  (selected row)

Table 2: 1, 0, 0
         0, 1, 0  (in return set)
         1, 1, 0  (in return set)
         0, 0, 1
         1, 0, 1
         0, 1, 1  (in return set)
         1, 1, 1  (in return set)

Example Two

         A, B, C    
Table 1: 0, 0, 1  (selected row)

Table 2: 1, 0, 0
         0, 1, 0  
         1, 1, 0 
         0, 0, 1  (in return set)
         1, 0, 1  (in return set)
         0, 1, 1  (in return set)
         1, 1, 1  (in return set)

How can I best accomplish this?

For example, this does not work:

SELECT        vw_fbScheduleFull.LocationName, vw_fbScheduleFull.FieldName, vw_fbScheduleFull.Description, vw_fbScheduleFull.StartTime, 
                         vw_fbScheduleFull.EndTime, vw_fbScheduleFull.LowerDivision, vw_fbScheduleFull.UpperDivision, vw_fbScheduleFull.SeniorDivision
FROM            (vw_fbScheduleFull INNER JOIN
                         fbDivision ON vw_fbScheduleFull.LowerDivision = fbDivision.LowerDivision AND fbDivision.LowerDivision = 1 OR 
                         vw_fbScheduleFull.UpperDivision = fbDivision.UpperDivision AND fbDivision.UpperDivision = 1 OR 
                         vw_fbScheduleFull.SeniorDivision = fbDivision.SeniorDivision AND fbDivision.SeniorDivision = 1)
WHERE        (vw_fbScheduleFull.PracticeDate = ?) AND (vw_fbScheduleFull.Locked IS NULL) AND (fbDivision.DivisionName = ?)
ORDER BY vw_fbScheduleFull.LocationName, vw_fbScheduleFull.FieldName, vw_fbScheduleFull.StartTime

Answer 1

It's not really a SQL problem you're asking, just a boolean expression problem. I assume you've got another column in these tables that allows you to join the rows in t1 to t2, but following your examples (where there is only 1 row in t1), you can do it as:

  SELECT t2.A2
       , t2.B2
       , t3.C2
    FROM t1
       , t2
   WHERE (t2.A2 OR NOT T1.A1)
     AND (t2.B2 OR NOT T1.B1)
     AND (t2.C2 OR NOT T1.C1)
;

I now see the non-abstracted answer you've posted above. Based on that, there are some issues in your SQL. For one thing, you should be expressing only the conditions in your JOIN clauses that relate the vw_fbScheduleFull table to the fbDivision table (i.e. the foreign/primary key relationship); all the LowerDivision/UpperDivision/SeniorDivision stuff should be in the WHERE clause.

Secondly, you're ignoring the operator precedence of the AND and OR operators - you want to enclose each of the *Division pairs within parens to avoid undesirable effects.

Not knowing the full schema of the tables, I would guess that the proper version of this query would look something like this:

  SELECT vw_fbScheduleFull.LocationName
       , vw_fbScheduleFull.FieldName
       , vw_fbScheduleFull.Description
       , vw_fbScheduleFull.StartTime
       , vw_fbScheduleFull.EndTime
       , vw_fbScheduleFull.LowerDivision
       , vw_fbScheduleFull.UpperDivision
       , vw_fbScheduleFull.SeniorDivision
    FROM vw_fbScheduleFull 
       , fbDivision
   WHERE vw_fbScheduleFull.PracticeDate = ?
     AND vw_fbScheduleFull.Locked IS NULL 
     AND fbDivision.DivisionName = ?
     AND (vw_fbScheduleFull.LowerDivision = 1 OR fbDivision.LowerDivision <> 1)
     AND (vw_fbScheduleFull.UpperDivision = 1 OR fbDivision.UpperDivision <> 1)
     AND (vw_fbScheduleFull.SeniorDivision = 1 OR fbDivision.SeniorDivision <> 1)
ORDER BY vw_fbScheduleFull.LocationName
       , vw_fbScheduleFull.FieldName
       , vw_fbScheduleFull.StartTime 
;

Looking one more time, I realize that your "fbDivision.DivisionName = ?" probably is reducing the number of rows in that table to one, and that there isn't a formal PK/FK relationship between those two tables. In which case, you should dispense with the INNER JOIN nomenclature in the FROM clause and just list the two tables; I've updated my example.

Answer 2

Steve Broberg's answer is correct and good; I'd just add that aliases can make queries much easier on the eyes:

SELECT f.LocationName
       , f.FieldName
       , f.Description
       , f.StartTime
       , f.EndTime
       , f.LowerDivision
       , f.UpperDivision
       , f.SeniorDivision
    FROM vw_fbScheduleFull f
       , fbDivision        d
   WHERE f.PracticeDate = ?
     AND f.Locked IS NULL 
     AND d.DivisionName = ?
     AND (f.LowerDivision = 1 OR d.LowerDivision <> 1)
     AND (f.UpperDivision = 1 OR d.UpperDivision <> 1)
     AND (f.SeniorDivision = 1 OR d.SeniorDivision <> 1)
ORDER BY f.LocationName
       , f.FieldName
       , f.StartTime 
;