Solved Join in Query giving odd results (1 Viewer)

GoodyGoody

Registered User.
Local time
Today, 09:52
Joined
Aug 31, 2019
Messages
120
Hi,

I have a table with World record running times in it with race Distance, Age and gender and the 3 primary keys. I know there is only 1 unique record in the database for 10k distance but there isalso a record for the 5k distance. I have a query which links to the WorldRecord table to pick up the WorldRecord information. So, the following query just joining without RaceDistance produces 2 lines for each runner as it is picking up both the 10k and 5k record. Which is exactly what I would expect:

SELECT RunnersForAnEventWithPBandWorldRecord.*, *
FROM RunnersForAnEventWithPBandWorldRecord inner JOIN worldrecord ON (RunnersForAnEventWithPBandWorldRecord.Gender = worldrecord.Gender) AND (RunnersForAnEventWithPBandWorldRecord.Age = worldrecord.Age);


HOWEVER, when I join on RaceDistance too the fields on my query which use the data from WorldRecord to calculate percentage of WorldRecord times are not populated at all implying that NO records are found. How can that be if the only change I am making is adding RaceDistance as another JOIN field?

SELECT RunnersForAnEventWithPBandWorldRecord.*, *
FROM RunnersForAnEventWithPBandWorldRecord LEFT JOIN worldrecord ON (RunnersForAnEventWithPBandWorldRecord.RaceDistance = worldrecord.RaceDistance) AND (RunnersForAnEventWithPBandWorldRecord.Age = worldrecord.Age) AND (RunnersForAnEventWithPBandWorldRecord.Gender = worldrecord.Gender);

And why does the INNER join suddenly become a LEFT JOIN? Sorry, not a great SQL expert.

As ever any help and insights really appreciated.
Thanks
GG
 

plog

Banishment Pending
Local time
Today, 04:52
Joined
May 11, 2011
Messages
11,613
Can you upload a sample database with the 3 objects in it?
 

GoodyGoody

Registered User.
Local time
Today, 09:52
Joined
Aug 31, 2019
Messages
120
Can you upload a sample database with the 3 objects in it?
Well it’s actually a lot more than that as there are several queries in a chain and then all the tables and linked data. I was hoping what I wrote would be enough. The unique record is there. If you do a simple select on the world record table with the 3 key elements it finds one single record. Why does adding one key in the joined query make it find no records and then why does it become a left join rather than an inner join?
 

plog

Banishment Pending
Local time
Today, 04:52
Joined
May 11, 2011
Messages
11,613
How about screenshots of data in your 2 datasources then?

I can't explain why it converts to a LEFT JOIN other than Microsoft always thinks it knows best. As for the data, a shot in the dark guess is that you are storing distances as text and there are differences between the 2 fields that the human eye can't easily see (like an extra space somewhere or a tab or a new line or sometlhing weird)
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 09:52
Joined
Sep 12, 2006
Messages
15,614
I cant see that this database would be full of sensitive data, so why not post the database. It will be much easier that way.

What does Age mean? What is the date type?

Is it Age of the record, Age of the athlete, Age at time of the record, or something else. Is it a date, a number, or a number of days. Linking tables based on data that changes, such as age, is very likely to produce errors, I would have thought.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:52
Joined
Feb 28, 2001
Messages
27,001
Regarding the LEFT JOIN/INNER JOIN question: Is there a NULL in any of the records of the WORLDRECORD table? Or do you already have a formal relationship between the first source and the WORLDRECORD source?

Regarding the SELECT, you have RunnersForAnEventWithPBandWorldRecord.*, * which actually is redundant. If you omit the first item (the one that is qualified by source name), that second * will return everything from both tables anyway.
 

GoodyGoody

Registered User.
Local time
Today, 09:52
Joined
Aug 31, 2019
Messages
120
For me the problem is why when I add the 3rd key the join becomes a left join. I investigated further and found a Combo box on a table field which was masking what the underlying database was seeing. I was seeing '10' as the racedistance but it was actually storing the RaceId which is 2. So thanks for your patience. Yes, DO NOT USE COMBO BOXES ON TABLE FIELDS!! :)
 

Isaac

Lifelong Learner
Local time
Today, 02:52
Joined
Mar 14, 2017
Messages
8,738
For me the problem is why when I add the 3rd key the join becomes a left join. I investigated further and found a Combo box on a table field which was masking what the underlying database was seeing. I was seeing '10' as the racedistance but it was actually storing the RaceId which is 2. So thanks for your patience. Yes, DO NOT USE COMBO BOXES ON TABLE FIELDS!! :)

When it comes to columns: (btw Fields are on a form, Columns are in a table)

Just a word of advice, you can spend years battling with the more "creative" datatypes, you might throw up your hands at the end and decide to just stick with a handful of simple datatypes, which there are rarely any good reasons to deviate from for 90% of your average data domains. Some of them are just gross from a principle perspective (like "checkbox" columns, which attempt to mix 'n match the concepts of Data vs. Presentation layers), and some are just bad ideas because they INCREASE the level of effort it will take to manage queries, display, forms, code references, etc.

This is also true on SQL Server. Despite the wide array of options for column types, the happiest successful developers stick with the simplest one that is sufficient for the job at hand. I.E., even Money is widely known to be problematic, and most people stick with Decimal(19,4). It is usually the newer developers who, in their enthusiasm for trying things out, try to use "everything" out there - and slowly grow to find reasons to reduce it back to a few in the end.

So the principle seems to hold true across various systems.

VERY few are the Access databases I create which have columns outside of Short Text, Long Text/Memo, DateTime, and Number (Long Integer).

Working at one of the biggest banks in the world SQL area for *all* call center and most auto finance data in the bank, I worked there for a year before I saw more than 4-5 datatypes in SQL Server columns - and those guys were experts, far above me. 4 types! A well oiled machine.

Simplicity works, even if it doesn't seem as cool at the time. Food for thought.
 

isladogs

MVP / VIP
Local time
Today, 09:52
Joined
Jan 14, 2017
Messages
18,186
When it comes to columns: (btw Fields are on a form, Columns are in a table)

No!
Access tables contain fields. Forms contain controls which may be bound to fields!

However, in SQL Server, tables do contain columns
 
Last edited:

Users who are viewing this thread

Top Bottom