Return also zero if not present....

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Return also zero if not present....

Post by sal21 »

The attached query return the quantity if the join is true, but i need also a zero quantity if the query return false...

similar:

ANTIPASTI 25
BIBITE 0
CONTORNI 5
DOLCI 0
FRUTTA 0
LIQUORI 0
PIZZE 0
PRIMI PIATTI 46
SECONDI DI CARNE 6
SECONDI DI PESCE 5
VINI 0

note:
peraphs just post a similar question but not found...
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Return also zero if not present....

Post by HansV »

Use LEFT JOIN instead of INNER JOIN.
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: Return also zero if not present....

Post by sal21 »

HansV wrote:
24 Mar 2022, 14:53
Use LEFT JOIN instead of INNER JOIN.
sorry bro...

but now have a where with DATA, but dont work

I NEED:
IDTAVOLO

1 0
2 1
3 0
4 2
5 0
6 0
7 0
8 2
9 0
10 0
11 0
12 0
13 0
14 0
15 1
16 0
17 0
18 0
19 0
20 1
21 0
22 0
23 0
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Return also zero if not present....

Post by HansV »

SELECT TAVOLI.IDTAVOLO, Val(Nz([N],0)) AS ConteggioDiTavoli
FROM TAVOLI LEFT JOIN (SELECT TAVOLI.IDTAVOLO, Count(COMANDA_TAVOLI.IDTIPO) AS N FROM COMANDA_TAVOLI INNER JOIN TAVOLI ON COMANDA_TAVOLI.IDTAVOLO = TAVOLI.IDTAVOLO WHERE COMANDA_TAVOLI.DATA=#3/25/2022# GROUP BY TAVOLI.IDTAVOLO) AS Q ON TAVOLI.IDTAVOLO = Q.IDTAVOLO;
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: Return also zero if not present....

Post by sal21 »

HansV wrote:
25 Mar 2022, 11:16
SELECT TAVOLI.IDTAVOLO, Val(Nz([N],0)) AS ConteggioDiTavoli
FROM TAVOLI LEFT JOIN (SELECT TAVOLI.IDTAVOLO, Count(COMANDA_TAVOLI.IDTIPO) AS N FROM COMANDA_TAVOLI INNER JOIN TAVOLI ON COMANDA_TAVOLI.IDTAVOLO = TAVOLI.IDTAVOLO WHERE COMANDA_TAVOLI.DATA=#3/25/2022# GROUP BY TAVOLI.IDTAVOLO) AS Q ON TAVOLI.IDTAVOLO = Q.IDTAVOLO;
:cheers:
tks work

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: Return also zero if not present....

Post by sal21 »

HansV wrote:
25 Mar 2022, 11:16
SELECT TAVOLI.IDTAVOLO, Val(Nz([N],0)) AS ConteggioDiTavoli
FROM TAVOLI LEFT JOIN (SELECT TAVOLI.IDTAVOLO, Count(COMANDA_TAVOLI.IDTIPO) AS N FROM COMANDA_TAVOLI INNER JOIN TAVOLI ON COMANDA_TAVOLI.IDTAVOLO = TAVOLI.IDTAVOLO WHERE COMANDA_TAVOLI.DATA=#3/25/2022# GROUP BY TAVOLI.IDTAVOLO) AS Q ON TAVOLI.IDTAVOLO = Q.IDTAVOLO;
SORRY...
but work in Access ide, and not in ADO vb6 code.

in vb6 have error in NZ
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Return also zero if not present....

Post by HansV »

SELECT TAVOLI.IDTAVOLO, IIf([N] Is Null,0,[N]) AS ConteggioDiTavoli
FROM TAVOLI LEFT JOIN (SELECT TAVOLI.IDTAVOLO, Count(COMANDA_TAVOLI.IDTIPO) AS N FROM COMANDA_TAVOLI INNER JOIN TAVOLI ON COMANDA_TAVOLI.IDTAVOLO = TAVOLI.IDTAVOLO WHERE COMANDA_TAVOLI.DATA=#3/25/2022# GROUP BY TAVOLI.IDTAVOLO) AS Q ON TAVOLI.IDTAVOLO = Q.IDTAVOLO;
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: Return also zero if not present....

Post by sal21 »

HansV wrote:
25 Mar 2022, 12:03
SELECT TAVOLI.IDTAVOLO, IIf([N] Is Null,0,[N]) AS ConteggioDiTavoli
FROM TAVOLI LEFT JOIN (SELECT TAVOLI.IDTAVOLO, Count(COMANDA_TAVOLI.IDTIPO) AS N FROM COMANDA_TAVOLI INNER JOIN TAVOLI ON COMANDA_TAVOLI.IDTAVOLO = TAVOLI.IDTAVOLO WHERE COMANDA_TAVOLI.DATA=#3/25/2022# GROUP BY TAVOLI.IDTAVOLO) AS Q ON TAVOLI.IDTAVOLO = Q.IDTAVOLO;
GREAT!