-1

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 Tables it prints out

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.

halfer
  • 19,824
  • 17
  • 99
  • 186
fredan
  • 13
  • 2
  • 1
    Downvote for stupid title. – user207421 Feb 21 '17 at 21:01
  • Just so you know, readers appreciate effort in posts, so "wanna" and "thx" are not advisable. We tend to say that if typing real words is too much trouble, Stack Overflow maybe is not for you. The reason for this is that questions are not just for the poster here - they are for many future readers. – halfer Feb 22 '17 at 00:21

1 Answers1

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.

EoinS
  • 5,405
  • 1
  • 19
  • 32
  • some people are just to good at this, thx man really appreciate it – fredan Feb 21 '17 at 21:02
  • is it possible to filter out one car make if needed? – fredan Feb 21 '17 at 21:11
  • 1
    Im new to this site so i apologize for any inconvenience i may have caused, if title still is bad then i apologize again for that. – fredan Feb 21 '17 at 21:32
  • @fredan you are all good. No inconvenience to me, it just helps other users gain benefit from these issues when they have similar problems – EoinS Feb 21 '17 at 22:18