CODEWITHSUNDEEP
mysqlcountforeign-keys
Réjôme
Réjôme

Reputation: 1484

How to perform a sql count based on a foreign key

Here is the simplified schema:

CREATE TABLE IF NOT EXISTS `person` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `HousingId` int(11) NOT NULL,
  PRIMARY KEY (`Id`)
);

CREATE TABLE IF NOT EXISTS `housing` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Capacity` int(11) NOT NULL,
  PRIMARY KEY (`Id`)
);

How can I simply get the free space for each housing ?

I tried the following query:

select housing.Id, housing.Capacity - count(person.Id)
from housing, person 
where person.HousingId = housing.Id 
group by housing.Id

But it works only for housing having at least one person, not for empty ones.

Thanks for your help !

Upvotes: 1

Views: 3114

Answers (1)

JoG
JoG

Reputation: 6732

Use a outer join 

select housing.Id, housing.Capacity - count(person.Id)
from housing
LEFT OUTER JOIN person ON person.HousingId = housing.Id 
group by housing.Id, housing.Capacity

Upvotes: 4

Related Questions