SQL Query: Left join with two conditions behaving like an inner join. (1 Viewer)

[ArtVents]

I did some searching before deciding to ask, but here is my issue.

select A.*
, B.Name
from Table1 as A
left join Table2 as B
on (A.Id = B.Id
and A.Variable = "Value");

This is my entire query. A.Variable represents one of three categories, and when I include "A.Variable = 'Value'" my query only returns rows that contain this value.

More context:
Because of issues beyond my control, I have to deal with three different categories that do not have a unique id. A unique identifier consists of the id + category. I need to pull in a name that correlates to the id + category combination.

Any help here would be awesome. I'm coming from programming in SAS, and Access has so far been a struggle.

 

[jdraw]

Might be better for readers to offer more focused advice if you
-told us in plain English what you are trying to achieve
-described the categories and how they apply
-showed us some data values that you are working with
-showed us what you expect as the result of the query based on your sample data values.

 

[ArtVents]

Might be better for readers to offer more focused advice if you
-told us in plain English what you are trying to achieve
-described the categories and how they apply
-showed us some data values that you are working with
-showed us what you expect as the result of the query based on your sample data values.

Fair point!
I have a query that unions 3 tables. This part works pretty well, but I need to pull in a multivalue variable that exists in all three. My instinct was to then join my post-union dataset to the original three tables to only pull the multivalue variable from their respective tables.

Categories are created in the query, and I am not able to edit the original tables. So, my hope was to join on the Id variable (which repeats in all three tables), but only join where category = X.

When I run the query with this "on" statement, it completely excludes the other two categories. If I add a second join, for one of the other two original tables, it returns no results (I'm assuming it's because the second join is on Id + category = Y.).

 

[theDBguy]

Fair point!
I have a query that unions 3 tables. This part works pretty well, but I need to pull in a multivalue variable that exists in all three. My instinct was to then join my post-union dataset to the original three tables to only pull the multivalue variable from their respective tables.

Categories are created in the query, and I am not able to edit the original tables. So, my hope was to join on the Id variable (which repeats in all three tables), but only join where category = X.

When I run the query with this "on" statement, it completely excludes the other two categories. If I add a second join, for one of the other two original tables, it returns no results (I'm assuming it's because the second join is on Id + category = Y.).

Hi. Welcome to AWF!

By using field=value, I think you're pretty much applying a filter against the data.

 

[ArtVents]

Hi. Welcome to AWF!

By using field=value, I think you're pretty much applying a filter against the data.

That feels bizarre to me. Working in other SQL environments, this would join on rows where the field matched, and ignore/return null for the ones that did not. Isn't a left join supposed to return all rows from the left table?

 

[ArtVents]

I figured out a solution.
In my left join, I changed the syntax to:

left join Table2 as B
on iif(Field = "Value", A.Id, null) = B.Id

This worked like a charm. Thank you everyone for the rubber ducking!

 

[theDBguy]

I figured out a solution.
In my left join, I changed the syntax to:


This worked like a charm. Thank you everyone for the rubber ducking!

Interesting, since Null can't be equal to anything, but glad to hear it worked for you. Cheers!

 

[Bullschmidt]

Left join with two conditions behaving like an inner join

The way I sometimes deal with that is having the criteria in subqueries and then the main left join in the main query.