473,320 Members | 1,957 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

LEFT JOIN does not Work as Expected???

twinnyfo
3,653 Expert Mod 2GB
Hey Folks!

Here's another extremely weird one, and I'm wondering if anyone has any insight as to why my LEFT JOIN is not working. Here is the Query:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblACCSelects.FullName, tblACCSelects.RelSRID 
  2. FROM tblACCSelects 
  3. LEFT JOIN tblSeniorRaters 
  4. ON tblACCSelects.RelSRID = tblSeniorRaters.SRID 
  5. WHERE (((tblSeniorRaters.Actual)=True) AND 
  6. ((tblSeniorRaters.Projected)=False));
Compare that with:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblACCSelects.FullName, tblACCSelects.RelSRID 
  2. FROM tblACCSelects 
  3. INNER JOIN tblSeniorRaters 
  4. ON tblACCSelects.RelSRID = tblSeniorRaters.SRID 
  5. WHERE (((tblSeniorRaters.Actual)=True) AND 
  6. ((tblSeniorRaters.Projected)=False));
Both of these queries return the exact, same results, even though, without question, there are quite a few records in tblACCSelects that have a RelSRID that is not found in tblSeniorRaters.

Have I somehow changed a setting that would prevent the most basic principles of SQL to suddenly stop working?

Any hepp would be appreciated.
Jun 21 '18 #1

✓ answered by Rabbit

To resolve, you need to move the filtering criteria into the join condition, or subquery the right side of the join with the criteria, or add (field is null or field = value) to each criteria that is on the right side.

I typically just use the first option.

15 3887
NeoPa
32,556 Expert Mod 16PB
Hi Twinny.

A very common confusion with OUTER JOINs

Every record that is in the LEFT JOIN dataset but not the INNER JOIN dataset is excluded by your WHERE clause. All of those records have NULL values for any of the Fields from the RIGHT side of the join, but you only let them through if they're True or False. Obviously, none can ever be either as they're always NULL.

NB. I've changed the title to be more accurate.
Jun 21 '18 #2
Rabbit
12,516 Expert Mod 8TB
To resolve, you need to move the filtering criteria into the join condition, or subquery the right side of the join with the criteria, or add (field is null or field = value) to each criteria that is on the right side.

I typically just use the first option.
Jun 21 '18 #3
twinnyfo
3,653 Expert Mod 2GB
It took me a few Goofy head-scratches to understand this, but if I do understand correctly, without changing the structure of the query, the following WHERE clause should produce my desired results? (I am not at work to check this)

Expand|Select|Wrap|Line Numbers
  1. WHERE (((tblSeniorRaters.Actual=True) 
  2.     Or (tblSeniorRaters.Actual Is Null)) 
  3.     AND 
  4. ((tblSeniorRaters.Projected=False) 
  5.     Or (tblSeniorRaters.Projected Is Null)));
Note: I was able to get the desired results by creating a separate query to identify the appropriate list of tblSeniorRatersSRID, and then LEFT JOINing that query. But, understanding why this works as it does, adds yet another tool to my kit.

Thanks, as usual, for the useful knowledge.
Jun 21 '18 #4
twinnyfo
3,653 Expert Mod 2GB
Note to Rabbit

you need to move the filtering criteria into the join condition
What does that mean, and could you provide an example?
Jun 21 '18 #5
Rabbit
12,516 Expert Mod 8TB
Option 1, moving it into the join.
Expand|Select|Wrap|Line Numbers
  1. SELECT tblACCSelects.FullName, tblACCSelects.RelSRID 
  2.  FROM tblACCSelects 
  3. LEFT JOIN tblSeniorRaters 
  4.  ON tblACCSelects.RelSRID = tblSeniorRaters.SRID AND
  5.  tblSeniorRaters.Actual=True AND 
  6.  tblSeniorRaters.Projected=False;
Option 2, subquery, which is basically what you did with creating the other query.
Expand|Select|Wrap|Line Numbers
  1. SELECT tblACCSelects.FullName, tblACCSelects.RelSRID 
  2.  FROM tblACCSelects 
  3. LEFT JOIN (
  4.  SELECT *
  5.  FROM tblSeniorRaters 
  6.  WHERE tblSeniorRaters.Actual=True AND 
  7.   tblSeniorRaters.Projected=False
  8. ) AS t
  9.  ON tblACCSelects.RelSRID = t.SRID;
Option 3 (which you have above in the other post), and this slipped my mind, produces different results. You would only use this is you wanted to exclude the rows from the left table that are in the right table but did not match the filter criteria. I don't think I've had to use this before. Pretty rare occurrence.
Expand|Select|Wrap|Line Numbers
  1. SELECT tblACCSelects.FullName, tblACCSelects.RelSRID 
  2.  FROM tblACCSelects 
  3. LEFT JOIN tblSeniorRaters 
  4.  ON tblACCSelects.RelSRID = tblSeniorRaters.SRID 
  5. WHERE (((tblSeniorRaters.Actual=True) 
  6.      Or (tblSeniorRaters.Actual Is Null)) 
  7.      AND 
  8.  ((tblSeniorRaters.Projected=False) 
  9.      Or (tblSeniorRaters.Projected Is Null)));
Jun 21 '18 #6
NeoPa
32,556 Expert Mod 16PB
TwinnyFo:
What does that mean
In short, the JOINs are always specified using an ON subclause. You can specify JOIN criteria, as opposed to dataset criteria (WHERE and HAVING), in there.
Jun 22 '18 #7
twinnyfo
3,653 Expert Mod 2GB
Rabbit, NeoPa,

I have been able to test these three options from Post #6. Obviously, Option 2 was successful, as that was my original workaround. I've been able to modify the query so that Option 3 also works. Here is my try at Option 1:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblACCSelects.FullName, tblACCSelects.RelSRID 
  2. FROM tblACCSelects 
  3. LEFT JOIN tblSeniorRaters 
  4. ON tblACCSelects.RelSRID = tblSeniorRaters.SRID 
  5. AND tblSeniorRaters.Actual = True 
  6. AND tblSeniorRaters.Projected = False;
However, when I try to execute this query, I get the Error JOIN expression not supported and line 5 is highlighted at the culprit.

Rather than simply dismiss this as an option, I'd prefer to work through it, to give me different options when working with these qureies. I htink right now, the two sharpest SQL guys I know of are engaged, so there must be something I must have mis-typed?

Any ideas?
Jun 22 '18 #8
NeoPa
32,556 Expert Mod 16PB
Hi Twinny.

I'm not sure what the exact restrictions are for the ON subclause. Personally I'd have expected it to work, but yield exactly the same results as the earlier version - IE. similar to the INNER JOIN.

Jet/ACE may have different syntax and allowable values from the actual SQL standard.

All that said, and as far as the Jet Help system goes, the ON subclause needs to be made up of criteria with a value from the LEFT table; a comparison operator; a value from the RIGHT table. That is to say that each criteria must be a comparison of some kind between two values that come one from each table.

Having explained the practicalities of the criteria within the ON subclause doesn't mean I suggest it for this situation. I can't see a version that would help - even if it were supported by Jet/ACE.

Rabbit may disagree. Although his main experience seems to be in T-SQL and the basic SQL standard itself, rather than in Jet/ACE per se, I look to him as my SQL guru too. Most of the clever stuff I've shared and use myself has come from playing with ideas he's introduced me to.
Jun 22 '18 #9
twinnyfo
3,653 Expert Mod 2GB
Friends,

Either way, whether I was able to get Rabbit's Option 1 to work or not, what I have added to my tool kit is a better understanding of LEFT JOIN queries with criteria, and I now have a different approach (or two) when faced with this situation.

Looking back at some of my queries, I now also see that Rabbit's Option 2 provides me a ton of possiblities--and I thank you for that.

One challenge is choosing the "Best Answer". Post #2 provided the answer "conceptually"; Post #3 provided generic examples which proved to work well and easily in Post #4. Post #6 provided examples for three viable options.

I lean toward Post #3 as the "Best Answer", as it clarified any remaining confusion I may have had approaching this problem.

Sincerest thanks to both of you. I learn something new every time!
Jun 22 '18 #10
Rabbit
12,516 Expert Mod 8TB
NeoPa is correct, I mainly work outside of Jet/ACE SQL these days.

As far as I'm aware, it'll work in the other major SQL databases; I can confirm it works in SQL Server and DB2. But I fired up Access and it doesn't like it, but fear not, there's a workaround. It's a bit hacky though.

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     tblACCSelects.FullName, 
  3.     tblACCSelects.RelSRID 
  4.  
  5. FROM tblACCSelects
  6.  
  7. LEFT JOIN tblSeniorRaters ON
  8.     tblACCSelects.RelSRID = tblSeniorRaters.SRID AND
  9.     tblSeniorRaters.Actual = iif(True, True, tblACCSelects.RelSRID) AND 
  10.     tblSeniorRaters.Projected = iif(True, False, tblACCSelects.RelSRID)
Basically, Access wants to see that fields from both sides of the join are represented in there. The hack then is to use iif to make it look like a field, any field, is being referenced from the other side but is never used because the iif always returns the true value.
Jun 22 '18 #11
twinnyfo
3,653 Expert Mod 2GB
Absolutely brilliant! And it works like a charm. It is hacky--but totally cool!

Thanks!
Jun 22 '18 #12
NeoPa
32,556 Expert Mod 16PB
Understanding why that works, and why that's different from including it in the WHERE clause, is a big step forward Twinny. If you haven't done that yet then I strongly advise that such time will be well spent.

PS. Though it's kludgy, it is a good illustration of the fundamental use of SQL. Furthermore, I was mistaken earlier when I said the concept Rabbit outlined was not able to help. Technically I was correct as I was unable to perceive it at the time, but there clearly is logic there that can be used.
Jun 23 '18 #13
twinnyfo
3,653 Expert Mod 2GB
Understanding why that works, and why that's different from including it in the WHERE clause, is a big step forward Twinny. If you haven't done that yet then I strongly advise that such time will be well spent.
NeoPa,

Yes, I do understand why it works. And that’s why it’s brilliant. I think I have become so dependent upon the Query Builder interface that I haven’t learned to think outside the box like Rabbit’s example. I never would have thought of such an approach, but the simple fact that it works the way it does shows how much I still have to learn!
Jun 23 '18 #14
Rabbit
12,516 Expert Mod 8TB
As a clarification, I don't actually suggest using that method for Access. It's more academic than anything.

It's the method I would use in other databases. But as far as Access is concerned, the "correct" method would be to use the subquery.

The biggest reason is that indexes probably would not work if you used that method. So a large join could suffer in performance. I do not know whether or not the index would be used as I'm not sure if the query optimizer in Access would recognize that the iif always returns the same value.
Jun 25 '18 #15
NeoPa
32,556 Expert Mod 16PB
Fair points Rabbit.

I did consider that. I suspect the indexed part itself would be sargable so it would fetch only those records that matched the index values, and it would then check each record for the other parts after loading, or at least identifying, them.

Certainly a proper RDBMS would be something you could be more comfortable relying on to optimise properly.
Jun 25 '18 #16

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Petre Agenbag | last post by:
Hi List Me again. I'm trying to return from multiple tables, the records that have field "information_sent" between two dates. The tables are all related by means of the id of the entry in the...
0
by: Soefara | last post by:
Dear Sirs, I am experiencing strange results when trying to optimize a LEFT JOIN on 3 tables using MySQL. Given 3 tables A, B, C such as the following: create table A ( uniqueId int not...
5
by: Marek Kotowski | last post by:
In MySQL online documentation there are some examples with multi-tables left joins. But all of them are like this (taken from the documentation): SELECT ... FROM table1 LEFT JOIN table2 on...
1
by: rossz | last post by:
I have this query to get the 10 most common products from in stock, based upon which parent category they are in: SELECT p.*,i.sql_inventory AS quantity FROM products AS p JOIN inv_cnt AS i ON...
14
by: Ryan | last post by:
I want to do the following SQL statement in Access. However, it won't allow me to have the secondary part of my join statement and tells me that this is not supported. OK, so Access doesn't support...
3
by: media.opslag | last post by:
Hi, How can i get this to work in access / jet sql ??? Someone?? SELECT tbl1., tbl2. FROM tbl1 left outer join tbl2 on
0
by: mlarson | last post by:
I have a program that worked fine then they needed to be able to also see the empty cells (inmate cells) on a housing unit when they ran the query. So what I had to do was take two tables and...
2
by: David F | last post by:
Hello, I have what seems like a simple left join query, but Access returns results like it's an inner join. I've tried numerous combinations without success. I have a table (ProjectList)...
2
by: SP | last post by:
I have two tables, A & B. Table B has adjustment factors of three types, "COMM" being one of them. I want a query that generates all rows of table A, and those where certain fields match, I want...
5
by: jimatqsi | last post by:
I have some code that works fine in an all Access environment. I am moving the back-end to SQL Server 2012. After moving the back-end I am getting this error when I run a particular query. ...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.