WHY dont show all month with where

[sal21]

Code: Select all

SELECT MESI.MM, MESI.MESE, Count(PRENOTAZIONI.IDTIP)
FROM PRENOTAZIONI RIGHT JOIN MESI ON PRENOTAZIONI.MM = MESI.M
WHERE PRENOTAZIONI.CANCL Is Null AND PRENOTAZIONI.AAAA="2022"
GROUP BY MESI.MM, MESI.MESE

WITH a where AAAA dont retrieve all month, i my case i need to show also month with zero result

[sal21]

without where AAAA

This post moved to here from a separate topic to provide context. StuartR

[HansV]

This is because your WHERE-condition requires records in PRENOTAZIONI to exist. Try this:

SELECT MESI.MM, MESI.MESE, Q.CONTA FROM MESI LEFT JOIN (SELECT MM, Count(IDTIP) AS CONTA FROM PRENOTAZIONI WHERE CANCL Is Null AND AAAA="2022" GROUP BY MM) AS Q ON MESI.MM = Q.MM ORDER BY MESI.MM

[sal21]

This is because your WHERE-condition requires records in PRENOTAZIONI to exist. Try this:

SELECT MESI.MM, MESI.MESE, Q.CONTA FROM MESI LEFT JOIN (SELECT MM, Count(IDTIP) AS CONTA FROM PRENOTAZIONI WHERE CANCL Is Null AND AAAA="2022" GROUP BY MM) AS Q ON MESI.MM = Q.MM ORDER BY MESI.MM

TKS bro.
but i need the result in the attached image ...

NOTE:
ANNO=2022

[HansV]

Please attach a sample database (zipped)

[Gasman]

MM is text in one table and Number in another?

[sal21]

Please attach a sample database (zipped)

Attached in last post

[sal21]

MM is text in one table and Number in another?

yes but you can use also M

[HansV]

SELECT MESI.MM, MESI.MESE, IIf(Q.CONTA Is Null,0,Q.CONTA) FROM MESI LEFT JOIN (SELECT MM, Count(IDTIP) AS CONTA FROM PRENOTAZIONI WHERE CANCL Is Null AND AAAA="2022" GROUP BY MM) AS Q ON MESI.M = Q.MM ORDER BY MESI.MM

[Gasman]

yes but you can use also M

Oh yes, that makes so much more sense.

So why is MM linked to MM and not M ?

[sal21]

yes but you can use also M

Oh yes, that makes so much more sense.

So why is MM linked to MM and not M ?

I need to use M or MM, with different property, in other part of code

[sal21]

SELECT MESI.MM, MESI.MESE, IIf(Q.CONTA Is Null,0,Q.CONTA) FROM MESI LEFT JOIN (SELECT MM, Count(IDTIP) AS CONTA FROM PRENOTAZIONI WHERE CANCL Is Null AND AAAA="2022" GROUP BY MM) AS Q ON MESI.M = Q.MM ORDER BY MESI.MM

AS USUAL EXCELLENT!
Tks.

[sal21]

SELECT MESI.MM, MESI.MESE, IIf(Q.CONTA Is Null,0,Q.CONTA) FROM MESI LEFT JOIN (SELECT MM, Count(IDTIP) AS CONTA FROM PRENOTAZIONI WHERE CANCL Is Null AND AAAA="2022" GROUP BY MM) AS Q ON MESI.M = Q.MM ORDER BY MESI.MM

OPS...

i need to add also a join FROM ANAGRAFICA INNER JOIN PRENOTAZIONI ON ANAGRAFICA.IDCLI=PRENOTAZIONI.IDCLI

new db attached

[HansV]

Do you need to return any fields from ANAGRAFICA in the query?

[sal21]

Do you need to return any fields from ANAGRAFICA in the query?

yes!

[HansV]

Which ones?

[sal21]

Which ones?

Sorry...
no.
is simple...i need the same result in the image attached but with the addition of this

...JOIN PRENOTAZIONI ON ANAGRAFICA.IDCLI=PRENOTAZIONI.IDCLI...

to the exists query:

SELECT MESI.MM, MESI.MESE, IIf(Q.CONTA Is Null,0,Q.CONTA) FROM MESI LEFT JOIN (SELECT MM, Count(IDTIP) AS CONTA FROM PRENOTAZIONI WHERE CANCL Is Null AND AAAA="2022" GROUP BY MM) AS Q ON MESI.M = Q.MM ORDER BY MESI.MM

[HansV]

What's the point of adding ANAGRAFICA if you don't want to show any field from that table?