Group by and count sql 2

[credo]

Hello,
I am doing a query using three joined tables Tbl_Villas, Tbl_Regions, Tbl_Countries..

I want to return the total number of villas per region, (including those regions that do not have any villas).

SELECT Tbl_Regions.RegionID, Tbl_Regions.Region, Count(Tbl_Villas.VillaID) AS CountOfVillaID
FROM (Tbl_Countries INNER JOIN Tbl_Regions ON Tbl_Countries.CountryID = Tbl_Regions.CountryID) INNER JOIN Tbl_Villas ON Tbl_Regions.RegionID = Tbl_Villas.Region
GROUP BY Tbl_Regions.RegionID, Tbl_Regions.Region;


I have my query returning the following:

RegionID Region CountofVillaID

1 Umbria 24
2 Tuscany 131
3 Provence 161
4 Cote D'Azur 77
5 St Tropez 25

I want also to include two other regions that don't have any villas listed in Tbl_Villas ie. the count is zero

10 Var 0
11 Dordrogne 0

How do I include zero count villas per region in my query ?


thanks in advance for any tips.

 

[cheerio]

Change the second INNER JOIN to LEFT JOIN to get all country/region combinations whether or not a villa exists

 

[TonyJollans]

Hi credo,

You want to use OUTER Joins instead of INNER Joins to do this.

Assuming you don't have any Countries without Regions (or, at least, don't want to report on them), all you need to do is change the Region/Villa Join ..

[blue][tt]SELECT Tbl_Regions.RegionID, Tbl_Regions.Region, Count(Tbl_Villas.VillaID) AS CountOfVillaID
FROM (Tbl_Countries INNER JOIN Tbl_Regions ON Tbl_Countries.CountryID = Tbl_Regions.CountryID) [red]LEFT[/red] JOIN Tbl_Villas ON Tbl_Regions.RegionID = Tbl_Villas.Region
GROUP BY Tbl_Regions.RegionID, Tbl_Regions.Region;[/tt][/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[credo]

Thanks Cheerio and Tony - that's solved it.