I have this schema in my sqlite database
CREATE TABLE `CARS` (
`ID` INTEGER,
`Name` TEXT,
PRIMARY KEY(ID)
);
CREATE TABLE `OWNERS` (
`ID` INTEGER,
`Name` TEXT,
PRIMARY KEY(ID)
);
And an intermediary table between OWNERS Table and CARS Table
CREATE TABLE `OwnerCars` (
`OwnerId` INTEGER,
`CarId` INTEGER,
PRIMARY KEY(OwnerId,CarId),
FOREIGN KEY(`OwnerId`) REFERENCES `Owners`(`Id`),
FOREIGN KEY(`CarId`) REFERENCES `Cars`(`Id`)
);
Querying this i'll get only owners that have cars and their car count
select ownerid, count(carid) as carscount from OwnerCars
inner join Owners on ownerid = id
group by(ownerid)
How can i get all owners (including those without cars and their car count as 0). Thanks for any help!
Use Left Outer Join to your query, also keep the owner table to the left.
select o.id, count(carid) as carscount
from owner o
Left Outer join OwnerCars on ownerid = o.id
group by(o.id)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments