Problem with left join.

  • Thread starter Thread starter Laurel
  • Start date Start date
L

Laurel

I want to select all of the rows in tblRLC for which Directory_HHD_Key =
True, and I want to join to them, the rows in RLC where Directory_Spouse =
True and Directory_HHD_Key is the same in both tables. (I diagram of what I
want is below). I choose the second of the 3 join types, "All records in
tblRLC and only those records in tblSpouse where the joined fields are
equal." The SQL version is below. My probem is that NO rows are returned
using this syntax. I've tried starting from scratch, and I've tried using
the default aliases (tblRLC and tblRLC_1). What am I not seeing here?

SELECT tblRLC.*, tblSpouse.*, tblRLC.Directory_HHD_Record,
tblSpouse.Directory_Spouse
FROM tblRLC LEFT JOIN tblRLC AS tblSpouse ON tblRLC.Directory_HHD_Key =
tblSpouse.Directory_HHD_Key
WHERE (((tblRLC.Directory_HHD_Record)=True) AND
((tblSpouse.Directory_Spouse)=True));
 
The problem that you are having is because you are requiring the selected
"match" records in tblSpouse to have a True value in the Directory_Spouse
field -- but when there is no "match" in tblSpouse for the LEFT JOIN, that
field will return a Null value, not a True value. Therefore, such records
will not be selected by your query, even though you want to display them (if
I'm understanding your intent correctly).

Jet has a bug when True/False fields return a Null value in this type of
query.

Therefore, you need to restructure the query to get the records from
tblSpouse (an alias of tblRLC's second instance) that match your desired
output. Assuming that you want only the matching records between the two
tables, this query will give you that:

SELECT tblRLC.*, tblSpouse.*, tblRLC.Directory_HHD_Record,
tblSpouse.Directory_Spouse
FROM tblRLC INNER JOIN tblRLC AS tblSpouse
ON tblRLC.Directory_HHD_Key =
tblSpouse.Directory_HHD_Key
WHERE (((tblRLC.Directory_HHD_Record)=True) AND
((tblSpouse.Directory_Spouse)=True));


If you want to return all records from tblRLC even if there is not a
matching record in tblSpouse, then try this:

SELECT tblRLC.*, tblSpouse.*, tblRLC.Directory_HHD_Record,
tblSpouse.Directory_Spouse
FROM tblRLC LEFT JOIN
(SELECT T.* FROM tblRLC AS T
WHERE T.Directory_Spouse=True)
AS tblSpouse ON tblRLC.Directory_HHD_Key =
tblSpouse.Directory_HHD_Key
WHERE tblRLC.Directory_HHD_Record=True;
 
Thank you! The second example was the one I wanted, and it works fine. I'm
not sure I completely understand the explanation, as it seems written for
the first example. But I will cherish the second example, and pattern match
when I need to solve this problem in the future. Thanks again!
 
In the second table, the query uses a subquery as the data source for the
"linking table" that you alias as tblSpouse. By doing this, you can put a
WHERE criterion directly on the records without worrying about a Null value
in the True/False field being returned by the subquery and causing the
problems that you were seeing before.


An alternative way of doing this, without the subquery, is to create a query
(call it qrySpouse) and save it in your .mdb file:

SELECT T.* FROM tblRLC AS T
WHERE T.Directory_Spouse=True;

Then create a second query like this:

SELECT tblRLC.*, tblSpouse.*,
tblRLC.Directory_HHD_Record,
tblSpouse.Directory_Spouse
FROM tblRLC LEFT JOIN
qrySpouse AS tblSpouse
ON tblRLC.Directory_HHD_Key =
tblSpouse.Directory_HHD_Key
WHERE tblRLC.Directory_HHD_Record=True;
 
Back
Top