How to LEFT JOIN with 2 conditions fails in Access and .NET Query Builder?

[David Carr]

Hi there,

I have been plagued with problems over the years when trying to get the
parentheses right for the following:

Table A:
A_ID - PK
A_Name
B_ID - Relationship to B.B_ID
with n records, some of which where B_ID is a NULL.

Table B:
B_ID - PK
MyFilterValue - Some value to filter on.

So my goal is to get all the records in table A, LEFT JOIN to the records in
table B providing they meet the filter criteria. Ignoring parentheses, I
typically do this by:
SELECT *
FROM A LEFT JOIN B ON A.B_ID = B.B_ID AND B.MyFilterValue = @SomeValue

The problem is that MS Access 2003 often balks at this. Eventually with
some tweaking of parentheses it ends up working. Unfortunately, the Query
Builder in VB.NET 2005 also balks and cannot be convinced to work otherwise.

If anyone can offer up general SQL performance/best practices advice on how
to achieve the goal, and/or how to resolve this in Access/VB.NET Query
Builder, that would be most appreciated.

Thanks in advance,
David

PS: I realise that another approach can be to use a subquery, such as:
SELECT *
FROM A LEFT JOIN (SELECT * FROM B WHERE B.MyFilterValue = @SomeValue) AS B_1
ON A.B_ID = B_1.B_ID
where Access seems to be less finicky with subqueries than dealing the top
statement and its parentheses. Aside from the reading something about
subqueries not being as effecient, regardless the VB.NET/JET provider still
chokes.

 

[KARL DEWEY]

I do not understand your '@SomeValue.' But try this for B.MyFilterValue
being a number ---
SELECT *
FROM A LEFT JOIN B ON A.B_ID = B.B_ID
WHERE B.MyFilterValue = SomeValue

Try this for B.MyFilterValue being text ---
SELECT *
FROM A LEFT JOIN B ON A.B_ID = B.B_ID
WHERE B.MyFilterValue = "SomeValue"

 

[David Carr]

Hi Karl,

Thx for the feedback. Sorry to not be clear - @SomeValue is a parameter (or
let it be some hard-coded value...it doesn't matter).

The approach you describe below doesn't work if the goal is to get ALL the
records from A. Putting the filter in the WHERE clause will reduce the
recordset down to only those records in A where MyFilterValue is SomeValue.
Subtle but important difference.

Your thoughts or anyone else? Ideally I am wondering what the
best/recommended practice is to solve this generic problem.

Thanks,
David

 

[KARL DEWEY]

Given the data below ---
Table A
B_ID
1
2
3
4
5

Table B
B_ID MyFilterValue
2
3 SomeValue
8 SomeValue

Do you want records 1 through 5 of A?
What different data is the B.MyFilterValue = "SomeValue" to provide?