How to count and join between two MySQL tables?

Question

I want to write out the number of times a car has been rented and I just cant get it to work, I have two tables. one is called vehicle and the other rental.

In rental is where all the "rentings" go when you book a car and all the vehicles is stored in vehicle. This is the query I made that almost works.

SELECT vehicle.id,COUNT(rental.vid) as rented,IFNULL(rental.vid,0) as nothing, vehicle.make as make, vehicle.model as model, vehicle.regnr as regnr, vehicle.color as color, vehicle.state as state, vehicle.imgurl as img, vehicle.description as description, vehicle.id 
FROM rental,vehicle 
WHERE vid = vehicle.id GROUP BY vid

and will print out this:

The rest which doesn't have a value (never been rented) isn't there, I have tried a lot of different ways with IFNULL but haven't gotten anywhere.

Answer 1

Select from Vehicle table and Left Join to Rental table. This will include Vehicle that have never been Rented and their Count(rental.vid) will be 0:

SELECT vehicle.id
,COUNT(rental.vid) as rented
, vehicle.make as make
, vehicle.model as model
, vehicle.regnr as regnr
, vehicle.color as color
, vehicle.state as state
, vehicle.imgurl as img
, vehicle.description as description
FROM vehicle
left join rental on vid = vehicle.id 
GROUP BY vehicle.id

Here is a simplified example

The implicit join you have in your example is equivalent to an inner join. With a left join you select all rows you want from your source table. If there are matches to the table you are left joining to, they will appear as well. It's a good way to append data to your source table.