LEFT JOIN

[Paul Digby]

I have a query shown below that does not return the right number of
countries. There are 34 Countries, but when the query runs, it only shows
26. I want the country to show, even if it does not meet the criteria,
either 0 or nothing


SELECT F1_Results.Country, Count(F1_Results.Team) AS Total
FROM F1_Countrys INNER JOIN F1_Results ON F1_Countrys.Country =
F1_Results.Country
WHERE (((F1_Results.Pos)='1') AND ((F1_Results.Team)='Ferrari'))
GROUP BY F1_Results.Country
ORDER BY F1_Results.Country;

 

[Gary Walter]

I have a query shown below that does not return the right number of
countries. There are 34 Countries, but when the query runs, it only shows
26. I want the country to show, even if it does not meet the criteria,
either 0 or nothing


SELECT F1_Results.Country, Count(F1_Results.Team) AS Total
FROM F1_Countrys INNER JOIN F1_Results ON F1_Countrys.Country =
F1_Results.Country
WHERE (((F1_Results.Pos)='1') AND ((F1_Results.Team)='Ferrari'))
GROUP BY F1_Results.Country
ORDER BY F1_Results.Country;

Hi Paul,

Your Subject says LEFT JOIN so I assume
you have found out that you cannot get meaningful
results when you try to filter the inner table of an
outer join -- you end up losing the effects of the
outer join.

Typical method is to filter in another query first,
then LEFT JOIN to the query.

Q1:

SELECT F1_Results.Country, Count(F1_Results.Team) AS Total
FROM F1_Results
WHERE (((F1_Results.Pos)='1') AND ((F1_Results.Team)='Ferrari'))
GROUP BY F1_Results.Country
ORDER BY F1_Results.Country;

then LEFT JOIN F1_Countrys to Q1

SELECT
F1_Countrys.Country,
NZ(Q1.Total, 0) As TeamTotal
FROM
F1_Countrys
LEFT JOIN
Q1
ON F1_Countrys.Country = Q1.Country
ORDER BY Q1.Country;

 

[Gary Walter]

that should be

SELECT
F1_Countrys.Country,
NZ(Q1.Total, 0) As TeamTotal
FROM
F1_Countrys
LEFT JOIN
Q1
ON F1_Countrys.Country = Q1.Country
ORDER BY F1_Countrys.Country;

 

[Paul Digby]

Works Great!

But, the Team = Ferrari, will change and needs to be a variable from an .asp
page called Team. Can this be done?

 

[Gary Walter]

Hi Paul,

Sorry, I do not "know asp."

In "regular" Access, I would
just code a rewrite of Q1 SQL
using new Team filter into a var
strSQL, then redefine the query.

Dim qdf As DAO.QueryDef
Dim strTeam As String
Dim strSQL As String

strTeam = <wherever you get it>

strSQL = "SELECT F1_Results.Country, " _
& "Count(F1_Results.Team) AS Total " _
& "FROM F1_Results " _
& "WHERE (((F1_Results.Pos)='1') AND " _
& "((F1_Results.Team)='" & strTeam & "')) " _
& "GROUP BY F1_Results.Country " _
& "ORDER BY F1_Results.Country;"

Set qdf = CurrentDb.QueryDefs("Q1")
qdf.SQL = strSQL

qdf.Close
Set qdf = Nothing

 

[Paul Digby]

I have now 4 out of 5 working. The last one includes a DISTINCT and I can
not get it to work without. Code as follows, help much appreciated. Do not
worry about .asp, I am passing variable back ok

SELECT Country, Team, Count(Team) AS TotalRaces
FROM [SELECT DISTINCT Country,race, team FROM F1_results ]. AS [%$##@_Alias]
WHERE Team = [@Team]
GROUP BY Country, Team
ORDER BY Country;

 

[Gary Walter]

I have now 4 out of 5 working. The last one includes a DISTINCT and I can
not get it to work without. Code as follows, help much appreciated. Do not
worry about .asp, I am passing variable back ok

SELECT Country, Team, Count(Team) AS TotalRaces
FROM [SELECT DISTINCT Country,race, team FROM F1_results ]. AS [%$##@_Alias]
WHERE Team = [@Team]
GROUP BY Country, Team
ORDER BY Country;

Hi Paul,

I just don't know....

I do know I would never use your alias name
in a query.

Possibly...

SELECT t.Country, t.Team, Count(t.Team) AS TotalRaces
FROM [SELECT DISTINCT Country,race, team FROM F1_results ]. AS t
WHERE t.Team = [@Team]
GROUP BY t.Country, t.Team
ORDER BY t.Country;

Sorry...

 

[Paul Digby]

Thanks, I will try that in a moment. The alias bit was put in by Access, I
just did not remove it, but will.

Now I have 4 out of 5 queries working correctly, I have joined them together
and used @Team to request the team and works fine. But, how and where would
I insert the where clause in the following statment

SELECT Query101a.Country, Query101a.Wins, Query104a.Laps,
Query103a.Finishes, Query102a.Podia
FROM ((Query101a INNER JOIN Query102a ON Query101a.Country =
Query102a.Country) INNER JOIN Query103a ON Query102a.Country =
Query103a.Country) INNER JOIN Query104a ON Query103a.Country =
Query104a.Country

I have now 4 out of 5 working. The last one includes a DISTINCT and I can
not get it to work without. Code as follows, help much appreciated. Do not
worry about .asp, I am passing variable back ok

SELECT Country, Team, Count(Team) AS TotalRaces
FROM [SELECT DISTINCT Country,race, team FROM F1_results ]. AS [%$##@_Alias]
WHERE Team = [@Team]
GROUP BY Country, Team
ORDER BY Country;

Hi Paul,

I just don't know....

I do know I would never use your alias name
in a query.

Possibly...

SELECT t.Country, t.Team, Count(t.Team) AS TotalRaces
FROM [SELECT DISTINCT Country,race, team FROM F1_results ]. AS t
WHERE t.Team = [@Team]
GROUP BY t.Country, t.Team
ORDER BY t.Country;

Sorry...