CODEWITHSUNDEEP
joinms-access-2007left-join
Steve
Steve

Reputation: 552

Left join with where clause not working

I was trying to get only selected rows from table A(not all rows) and rows matching table A from table B, but it shows only matching rows from table A and table B, excluding rest of the selected rows from table A.

I used this condition,

SELECT A.CategoryName,B.discount
    from A LEFT JOIN B ON A.CategoryCode = B.CategoryCode 
    WHERE A.itemtype='F' and B.party_code=2



i have 2 tables:

table 1: A with 3 columns
CategoryName,CategoryCode(PK),ItemType

table 2: B with 2 columns
CategoryCode(FK),Discount,PartyCode(FK)(from another table)

NOTE: working in access 2007

Upvotes: 1

Views: 4036

Answers (2)

mlinth
mlinth

Reputation: 3108

For non-matching rows from table B, party_code = NULL, so your where clause will evaluate to false and therefore the row won't be returned. So, you need to filter the "B" records before joining. Try

SELECT A.CategoryName,B.discount
    from A LEFT JOIN B ON A.CategoryCode = B.CategoryCode  and B.party_code=2 
    WHERE A.itemtype='F'

[EDIT] That doesn't work in Access. next try.

You can create a query to do your filter. Let's call it "B_filtered". This is just

SELECT * FROM B where party_code = 2

(You could make the "2" a parameter to make it more flexible).

Then, just use this query in your actual query.

SELECT A.CategoryName,B_filtered.discount
    from A LEFT JOIN B_filtered ON A.CategoryCode = B_filtered.CategoryCode 
    WHERE A.itemtype='F'

[EDIT]

Just Googled - I think you can do this directly with a subquery.

SELECT A.CategoryName,B_filtered.discount
        from A LEFT JOIN (SELECT * FROM B where party_code = 2) AS B_filtered ON A.CategoryCode = B_filtered.CategoryCode 
        WHERE A.itemtype='F'

Upvotes: 1

AWinkle
AWinkle

Reputation: 673

What mlinth proposed is correct, and would work for most other SQL languages. The query below is the same basic concept but using a null condition.

Try: 

SELECT A.CategoryName,B.discount
from A LEFT JOIN B ON A.CategoryCode = B.CategoryCode 
WHERE A.itemtype='F'   and (B.party_code=2 OR B.party_code IS NULL)

If party_code is nullable, switch to using the PK or another non-nullable field.

Upvotes: 1

Related Questions