Member Avatar for fabzster

Hi

I use the following Query:

SELECT DISTINCT COUNT(tblpolicies.PolicyNumber)AS Figures, tblstores.GroupName, tblstores.StoreName, tblstores.StoreType FROM tblpolicies, tblstores
                     WHERE DateReceived BETWEEN '2012-06-01' AND '2012-06-30' AND tblstores.StoreType='FLAGSHIP' AND tblpolicies.PolicyStatus='ACTIVE' AND tblstores.StoreStatus='ACTIVE' AND tblpolicies.StoreId=tblstores.StoreId
GROUP BY StoreName ORDER BY Figures DESC

What this does is give me a result:

95 GroupName StoreName StoreType

This will only show me stores that have sold policies between the specified dates.

How would I go about showing all stores even if they did not have any policies sold in the specific periods and have them display a 0 for figures.

Edited by fabzster
  • 2 Contributors
  • 1 Reply
  • 58 Views
  • 34 Minutes Discussion Span
  • Latest Post Latest Post by pritaeas
Member Avatar for pritaeas

You would have to change your join to something like this:

FROM tblstores LEFT JOIN tblpolicies ON tblpolicies.StoreId=tblstores.StoreId
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.