CODEWITHSUNDEEP
mysqlsql
tomsky
tomsky

Reputation: 545

Mysql subquery with joins

I have a table 'service' which contains details about serviced vehicles. It has an id and Vehicle_registrationNumber which is a foreign key. Whenever vehicle is serviced, a new record is made. So, for example if I make a service for car with registration ABCD, it will create new row, and I will set car_reg, date and car's mileage in the service table (id is set to autoincreament) (e.g 12 | 20/01/2012 | ABCD | 1452, another service for the same car will create row 15 | 26/01/2012 | ABCD | 4782).

Now I want to check if the car needs a service (the last service was either 6 or more months ago, or the current mileage of the car is more than 1000 miles since last service), to do that I need to know the date of last service and the mileage of the car at the last service. So I want to create a subquery, that will return one row for each car, and the row that I'm interested in is the newest one (either with the greatest id or latest endDate). I also need to join it with other tables because I need this for my view (I use CodeIgniter but don't know if it's possible to write subqueries using CI's ActiveRecord class) 

SELECT * FROM (
SELECT *
FROM (`service`)
JOIN `vehicle` ON `service`.`Vehicle_registrationNumber` = `vehicle`.`registrationNumber`
JOIN `branch_has_vehicle` ON `branch_has_vehicle`.`Vehicle_registrationNumber` = `vehicle`.`registrationNumber`
JOIN `branch` ON `branch`.`branchId` = `branch_has_vehicle`.`Branch_branchId`
GROUP BY `service`.`Vehicle_registrationNumber` )
AS temp


WHERE `vehicle`.`available` != 'false'
AND `service`.`endDate` <= '2011-07-20 20:43'
OR service.serviceMileage  < vehicle.mileage - 10000

Upvotes: 0

Views: 202

Answers (2)

rauschen
rauschen

Reputation: 3996

SELECT `service`.`Vehicle_registrationNumber`, Max(`service`.`endDate`) as lastService,
   MAX(service.serviceMileage) as lastServiceMileage, vehicle.*
FROM `service`
INNER JOIN `vehicle` 
  ON `service`.`Vehicle_registrationNumber` = `vehicle`.`registrationNumber`
INNER JOIN `branch_has_vehicle` 
  ON `branch_has_vehicle`.`Vehicle_registrationNumber` = `vehicle`.`registrationNumber`
INNER JOIN `branch` 
  ON `branch`.`branchId` = `branch_has_vehicle`.`Branch_branchId`
WHERE vehicle.available != 'false'
GROUP BY `service`.`Vehicle_registrationNumber` 
HAVING lastService<=DATE_SUB(CURDATE(),INTERVAL 6 MONTH) 
   OR lastServiceMileage < vehicle.mileage - 10000

;

I hope I have no typo in it ..

Upvotes: 1

Luis
Luis

Reputation: 1294

If instead of using * in the subquery you specify the fields you need (which is always good practice anyway), most databases have a MAX() function that returns the maximum value within the group. Actually, you don't even need the subquery. You can do the joins and use the MAX in the SELECT statement. Then you can do something like 

SELECT ...., MAX('service'.'end_date') AS LAST_SERVICE
...
GROUP BY 'service'.'Vehicle_registrationNumber'

Or am I missing something?

Upvotes: 1

Related Questions