LEFT JOIN with selection criteria

[IfButOnly]

I have two tables joined thru the ID field

TableA
======
ID, fldA, fldB

TableB
=====
BID, fldC, fldD

I am wanting to list all records from TableA and those fields from TableB where fldD = X.

My current SQL, which works fine, to print all records is:

SELECT TableA.fldA, TableA.fldB TableB.fldC TableB.fldD FROM TableA LEFT JOIN TableB ON TableA.ID = TableB.BID

I am having trouble finding the solution when I use selection criteria. I expected the SQL to look like:

SELECT TableA.fldA, TableA.fldB TableB.fldC TableB.fldD FROM TableA LEFT JOIN TableB ON TableA.ID = TableB.BID WHERE TableB.fldD = "X"

This only shows TableA records where TableB.fldD = "X"

Other than my explanation, I am sure this is fairly simple.

Can anyone point me in the right direction
Thanks..

 

[DLB]

You can first build a query to pull the records from TableB Where fldD ="X".

Then in a second query, left join TableA with the first query.

Run the second query.

 

[IfButOnly]

DLB, That's what I wanted.. thanks vm.

 

[FoFa]

Or you could use one query as this:
SELECT TableA.fldA, TableA.fldB TableB.fldC TableB.fldD FROM TableA LEFT JOIN TableB ON TableA.ID = TableB.BID AND TableB.fldD = "X"

 

[IfButOnly]

Thanks FoFa

 

[IfButOnly]

I tried the solution suggested by FoFa and got the error - Join expression not supported.

My actual SQL is:-

SELECT tPlayers.ID, tPlayers.LastName, tPlayers.FirstName, tPlayers.Handicap, tPlayers.Status, tPlayers.Comment, tScores.PlayDay, tScores.DayHandicap, tScores.GrossScore
FROM tPlayers LEFT JOIN tScores ON tPlayers.ID = tScores.PlayerID AND tScores.PlayDay = 1;

Any thoughts?

 

[Chatbox]

Did you try replacing your last "AND" with a "WHERE"?

 

[DLB]

You will need to put the join expression in brackets.

SELECT tPlayers.ID, tPlayers.LastName, tPlayers.FirstName, tPlayers.Handicap, tPlayers.Status, tPlayers.Comment, tScores.PlayDay, tScores.DayHandicap, tScores.GrossScore
FROM tPlayers LEFT JOIN tScores ON (tPlayers.ID = tScores.PlayerID AND tScores.PlayDay = 1);


It's a non-equi join. You cannot switch the query to Design View.

When the query is saved, Access may remove the brackets, so the next time you edit the query, you may need to put in the brackets again.

Not sure if Access would optimise such a join (will need two fairly large tables for testing) though I doubt it very much.

 

[Pat Hartman]

You can use the two query solution that someone suggested earlier. Or, you can use the query you had originally. You simply need to allow for nulls:

SELECT TableA.fldA, TableA.fldB TableB.fldC TableB.fldD FROM TableA LEFT JOIN TableB ON TableA.ID = TableB.BID WHERE TableB.fldD = "X" OR TableB.fldD Is Null;

 

[DLB]

Pat,
The poster wanted to include all records from TableA in the results.

I am wanting to list all records from TableA and those fields from TableB where fldD = X.

Using WHERE TableB.fldD = "X" OR TableB.fldD Is Null will exclude those TableA.IDs which exist in TableB.BID but none of whose fldD is equal to "X".

For instance, if TableB contains these two records for BID=4:

BID	fldC	fldD
4	bbb	yyy
4	ccc	zzz

TableA.ID=4 will not be returned by the query because none of the values in fldD is equal to X.

~

 

[IfButOnly]

tried the expression in brackets

SELECT tPlayers.LastName, tPlayers.FirstName, tPlayers.Handicap, tScores.PlayDay, tScores.GrossScore
FROM tPlayers LEFT JOIN tScores ON (tPlayers.ID = tScores.PlayerID AND tScores.PlayDay = 1);

but it only returned records on both sides (like an Inner join).

Using the seperate Select for the tScores table worked, but as I am reading, modifying and recreating the Query in code, I would love to be only updating the one query.

Many thanks for the feedback so far, Any further thoughts appreciated...

 

[Pat Hartman]

SELECT TableA.fldA, TableA.fldB TableB.fldC TableB.fldD FROM TableA LEFT JOIN TableB ON TableA.ID = TableB.BID WHERE TableB.fldD = "X" OR TableB.fldD Is Null;

- This query doesn't work. It returns rows from tblA that have a match in tblB with fldD = "X" and it returns rows from tblA that have no match at all in tblB.

The only solution I have found that works is the two query solution (a subselect should also work but will be slower). Create a query that selects the rows from tblB with fldD = "X". Then in the second query join tblA to that query with a left join. The result is all rows from tblA and any matching row from tblB with fldD = "X"

Using the seperate Select for the tScores table worked, but as I am reading, modifying and recreating the Query in code, I would love to be only updating the one query.

- This is less efficient than working with stored querydefs.

 

[IfButOnly]

Thanks Pat, I will revert to the two query method.

I am using stored querydef, but instead of setting the criteria via Forms!formname.control etc.... I am getting the qdf via code, modifying it and recreating the stored querydef. I am doing this mainly because of my intense hatred of the first technique during development and partly because I learnt how to do it.

Is this particularly inefficient?

thanks for help to date... Peter

 

[Pat Hartman]

Running SQL strings is less efficient thant running stored querydefs with parameters. Queries are optimized an execution plans are created and saved when the querydef is saved. All this work needs to be done EACH time a stored SQL string is run so it might need to happen dozens of times for each database session. This adds additional time but unless you are running the query in a loop, probably not enough to notice. The real problem this causes is database bloat. Preparing these plans takes a fair amount of working space and that space is not recovered until the db is compacted.

 

[IfButOnly]

thanks Pat - that makes a lot of sense. I will either overcome my parameter query/subform development phobia or test with parameter held in subform until I'm through testing.

thanks all for your input on this... Peter