bad query day : the right join does not work

Topic

New Reply

I have a query with right or is it a left join
SELECT Ref_SAE.Code, Count(Ref_SAE.Code) AS CompteDeCode
FROM Ref_SAE LEFT JOIN ETA ON Ref_SAE.Code = ETA.CodeSAE
WHERE (((ETA.StatutCSST)=Yes))
GROUP BY Ref_SAE.Code
ORDER BY Ref_SAE.Code;

the Ref_SAE table has 32 catégories the eta table has some 12 000 cases joined by the variable codeSAE. But ou the table has no results for REF_SAE 19 and 31.
I wanted my query to give the count for all 32 categories meaning a zero in the listing for code = 19 and 31

this is what I get
code count
1 2372
2 22
3 67
4 30
5 190
6 162
7 57
8 39
9 2
10 45
11 385
12 93
13 58
14 13
15 547
16 2750
17 3
18 40
20 19
21 3116
22 106
23 70
24 2
25 23
26 773
27 28
28 323
29 488
30 1106
32 70

I thought this would be simple

Reply | Quote

Replies

Give this a try:

Code:

SELECT Ref_SAE.Code, Count(ETA_SAE.Code) AS CompteDeCode
FROM Ref_SAE LEFT JOIN ETA ON Ref_SAE.Code = ETA.CodeSAE
WHERE (((ETA.StatutCSST)=Yes))
GROUP BY Ref_SAE.Code
ORDER BY Ref_SAE.Code;

I tested using a couple of files I have using this query:

Code:

SELECT Agencies.Agency, Count([Contracts]![Agency]) AS ContractCnt
FROM Agencies LEFT JOIN Contracts ON Agencies.Agency = Contracts.Agency
GROUP BY Agencies.Agency
ORDER BY Agencies.Agency;

I received the output below:

I hope this helps.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

SELECT Ref_SAE.Code, Count(Ref_SAE.Code) AS CompteDeCode
FROM Ref_SAE LEFT JOIN ETA ON Ref_SAE.Code = ETA.CodeSAE
WHERE (((ETA.StatutCSST)=Yes))
GROUP BY Ref_SAE.Code
ORDER BY Ref_SAE.Code;

You cannot do this with outer joins if you are querying the child table,
you need to set up a query first on the child table, then link to the child query with your outer join otherwise the where will eliminate entries that have no entry

Create a query on child first such as

Code:

SELECT ETA.CodeSAE FROM ETA WHERE ETA.StatutCSST=True

Save this as say qryETACSSTStatusYesCodes

Then Create a Left Join from Ref Table to this

Code:

SELECT Ref_SAE.Code, Count(Ref_SAE.Code) AS CompteDeCode
FROM Ref_SAE LEFT JOIN qryETACSSTStatusYesCodes 
ON Ref_SAE.Code = qryETACSSTStatusYesCodes .CodeSAE
GROUP BY Ref_SAE.Code
ORDER BY Ref_SAE.Code;

An alternative would be to use a SUB Query, but in Access this can be quite slow.

Reply | Quote

this worked

SELECT Ref_SAE.Code, Ref_SAE.Libelle, [sousR_eta sae].CompteDeCodeEtablissement
FROM Ref_SAE LEFT JOIN [sousR_eta sae] ON Ref_SAE.Code = [sousR_eta sae].CodeSAE
ORDER BY Ref_SAE.Code;

where [sousR_eta sae] =

SELECT ETA.CodeSAE, Count(ETA.CodeEtablissement) AS CompteDeCodeEtablissement
FROM ETA
WHERE (((ETA.StatutCSST)=Yes))
GROUP BY ETA.CodeSAE
ORDER BY ETA.CodeSAE;

But can someone give me a link to a microst page that explains why my original query would not work.

Reply | Quote

Your original query would not work, because your WHERE is applying the filter to the result set of the main query.
So although the LEFT Join will return all rows from the parent,
the WHERE (((ETA.StatutCSST)=Yes)) targets the result set and thus eliminates any rows that do not match this condition,
which must be any row that is NOT in the child table.
By creating the query on the child as a separate query, then the filter is applied to this on it’s own first.
When this is joined to the Parent table with a left join, the result set is a standard outer join query returning all rows from the parent.

Reply | Quote