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...
Return also zero if not present....
-
- PlatinumLounger
- Posts: 4334
- Joined: 26 Apr 2010, 17:36
Return also zero if not present....
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78241
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- PlatinumLounger
- Posts: 4334
- Joined: 26 Apr 2010, 17:36
Re: Return also zero if not present....
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.
-
- Administrator
- Posts: 78241
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Return also zero if not present....
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;
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
Hans
-
- PlatinumLounger
- Posts: 4334
- Joined: 26 Apr 2010, 17:36
Re: Return also zero if not present....
HansV wrote: ↑25 Mar 2022, 11:16SELECT 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;
tks work
-
- PlatinumLounger
- Posts: 4334
- Joined: 26 Apr 2010, 17:36
Re: Return also zero if not present....
SORRY...HansV wrote: ↑25 Mar 2022, 11:16SELECT 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;
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.
-
- Administrator
- Posts: 78241
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Return also zero if not present....
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;
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
Hans
-
- PlatinumLounger
- Posts: 4334
- Joined: 26 Apr 2010, 17:36
Re: Return also zero if not present....
GREAT!HansV wrote: ↑25 Mar 2022, 12:03SELECT 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;