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
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