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.

 

[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...

 

[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

 

[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