Counting 0 values

  • Thread starter Thread starter Adam Pedder
  • Start date Start date
A

Adam Pedder

I have the following query:

SELECT dbo_Lightboxes.lightboxname, dbo_Lightboxes.lightboxid,
Count(dbo_lightpic.pictureid) AS CountOfpictureid
FROM dbo_Lightboxes INNER JOIN dbo_lightpic ON dbo_Lightboxes.lightboxid =
dbo_lightpic.lightboxid
GROUP BY dbo_Lightboxes.lightboxname, dbo_Lightboxes.lightboxid,
dbo_Lightboxes.userid
HAVING (((dbo_Lightboxes.userid)=[?]));

Simple enough, it returns the lightboxname and lightboxid for any given
userid, along with a count of the number of the number of pictures in it.

My problem is that it only returns results for light boxes that have
pictures in them, whereas I want it to return them with a count of 0.

Can anyone push me in the right direction?

Thanks,

Adam
 
In query design view, double-click the line joining your 2 tables.
Access pops up a dialog offering 3 choices.

The SQL statement will change to an outer join.
 
Thanks, I've never been good a joins.

That was the quickest reply I've ever had to a newsgroup!

Adam

Allen Browne said:
In query design view, double-click the line joining your 2 tables.
Access pops up a dialog offering 3 choices.

The SQL statement will change to an outer join.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Adam Pedder said:
I have the following query:

SELECT dbo_Lightboxes.lightboxname, dbo_Lightboxes.lightboxid,
Count(dbo_lightpic.pictureid) AS CountOfpictureid
FROM dbo_Lightboxes INNER JOIN dbo_lightpic ON dbo_Lightboxes.lightboxid =
dbo_lightpic.lightboxid
GROUP BY dbo_Lightboxes.lightboxname, dbo_Lightboxes.lightboxid,
dbo_Lightboxes.userid
HAVING (((dbo_Lightboxes.userid)=[?]));

Simple enough, it returns the lightboxname and lightboxid for any given
userid, along with a count of the number of the number of pictures in it.

My problem is that it only returns results for light boxes that have
pictures in them, whereas I want it to return them with a count of 0.

Can anyone push me in the right direction?

Thanks,

Adam
Click to expand...
Click to expand...
 
Back
Top