Count need to return 0

[Shurikn]

Here's my problem I have a sql string

sql="SELECT PubliciteArchive.ID, PubliciteArchive.Nom, PubliciteArchive.Type,PubliciteArchive.Duree,ClientArchive.Nom as Compagnie, Count(PresentationArchive.PubID) AS nbPres "+
"FROM PubliciteArchive, PresentationArchive, ClientArchive "+
"WHERE (((PubliciteArchive.ID)=[PubID] AND ClientArchive.ID=PubliciteArchive.ClientID) AND DatePResentation > #"+dtpDebut.Value+"# AND DatePresentation < #"+dtpFin.Value+"#) "+
"GROUP BY PubliciteArchive.ID, PubliciteArchive.Nom , PubliciteArchive.Type,PubliciteArchive.Duree,ClientArchive.Nom";

 

that is suposed to return me an ad with it's name, it's compagny name and the number of time it played between the dates that are in 2 DatePicker.

The string return me the corect number of time and all the info I want, execpt that if there's no paying between the dates, instead of returning 0, it does not return a row.

Is there a way to make it return a row with a 0 or should I create an ad class and store all the ad in an arrays of ads and then filter the dates?

Edited July 25, 2005 by Shurikn

[mhildner]

I'm guessing normally your query would return one row? Then you use the "Count(PresentationArchive.PubID)" column to get your number to use?

 

You can either use the method your using, check to see that the row count = 0. If there are no rows returned, then just put a zero in there.

 

Alternatively, if you're only interested in the count, and no other column information, you could modify your query to be something like "SELECT COUNT(... WHERE...". Then you can ExecuteScalar and just retrieve the value of COUNT.

[Shurikn]

no lets say my 3 tables have these:

 

Publicity:

ID        name       compId

1          a              1
2          b              1
3          c              1
4          omg          2

Compagny:

ID          Name      

1            M$
2            Apple

 

Presentations

PubID        Time
1               2005/07/25 06:05:23
2               2005/07/25 07:05:23
2               2005/07/25 08:05:23
4               2005/07/25 09:05:23
1               2005/07/25 10:05:23
1               2005/07/26 06:05:23

 

I want my query to return me:

Count(PubID)      pub.name        compagny.name
3                       a                   M$
2                       b                   M$
0                       c                   M$
1                       omg               Apple

(I ommited certain collumn, but I'm pretty sure you now understand what I mean)

 

I get these result just fine, if i dont put the date restrains. lets say I want publicity between 2005/07/25 07:05:22 and 2005/07/25 08:05:24

 

I should get:

Count(PubID)      pub.name        compagny.name
0                       a                   M$
2                       b                   M$
0                       c                   M$
0                       omg               Apple

but I only get:

Count(PubID)      pub.name        compagny.name
2                       b                   M$

[Joe Mamma]

you need a left join, something like this:

 

SELECT pub.ID, pub.Nom, pub.Type, pub.Duree, 
client.Nom as Compagnie, Count(pres.PubID) AS nbPres 
FROM 
  [b](client inner join pub on client.id = pub.id) [/b]
[b]	left join pres on pub.id = pres.id
[/b]WHERE 
  pub.ID=PubID AND DatePResentation > #[lodate]# 
  AND DatePresentation < #[hidate#
GROUP BY 
  client.Nom,  pub.ID, pub.Nom, pub.Type, pub.Duree

[Shurikn]

I still have only the record wich have presentations :/ I guess at this point i should do 2 query instead...

[Joe Mamma]

try:

 

SELECT pub.ID, pub.Nom, pub.Type, pub.Duree, 
client.Nom as Compagnie, Count(pres.PubID) AS nbPres 
FROM 
[b](client [size=3]left join[/size] pub on client.id = pub.id) [/b]
[b]	left join pres on pub.id = pres.id[/b]
WHERE 
pub.ID=PubID AND DatePResentation > #[lodate]# 
AND DatePresentation < #[hidate]#
GROUP BY 
client.Nom, pub.ID, pub.Nom, pub.Type, pub.Duree