JOIN 3 tables and (LIMIT 2 rows ORDER BY time DESC)

Question

I know this question has been asked many times, but I am having trouble implementing it.
I have made a cut down example so its easy to reproduce.
I want to join 3 tables but on the last one I want to limit to 2 rows DESC

CREATE TABLE `cars` (
`car_id` int(11) NOT NULL AUTO_INCREMENT,
`plate` varchar(10) NOT NULL,
`km` int(11) NOT NULL,
`status` tinyint(1) NOT NULL,
PRIMARY KEY (`car_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
INSERT INTO `cars` (`car_id`, `plate`, `km`, `status`) VALUES
(1, 'ABC1234', 130123, 1),
(2, 'DEF1234', 100123, 1),
(3, 'QWE1234', 5000, 1),
(4, 'ASD1234', 3000, 1),
(5, 'ZXC1234', 23000, 0);

---

CREATE TABLE `cars_to_users` (
`car_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
UNIQUE KEY `car_id` (`car_id`,`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `cars_to_users` (`car_id`, `user_id`) VALUES
(1, 1),
(2, 1),
(3, 2),
(4, 2),
(5, 2);

---

CREATE TABLE `service` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`car_plate` varchar(10) NOT NULL,
`s_timestamp` int(10) NOT NULL,
`price` double NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=111 ;
INSERT INTO `service` (`id`, `car_plate`, `s_timestamp`, `price`) VALUES
(1, 'ABC1234', 1312300100, 30),
(2, 'DEF1234', 1312300100, 15),
(3, 'QWE1234', 1312300100, 16),
(4, 'ASD1234', 1312300100, 50),
(5, 'ABC1234', 1312300200, 50),
(6, 'DEF1234', 1312300200, 25),
(7, 'QWE1234', 1312300200, 30),
(8, 'ABC1234', 1312300300, 20),
(9, 'ASD1234', 1312300300, 60),
(10, 'ABC1234', 1312300400, 15),
(11, 'ASD1234', 1312300400, 20);

What I want is this

car_id  plate  km  car_plate  s_timestamp  price  
3 QWE1234 5000 QWE1234 1312300200 30 
3 QWE1234 5000 QWE1234 1312300100 16 
4 ASD1234 3000 ASD1234 1312300400 20 
4 ASD1234 3000 ASD1234 1312300300 60 

2 rows from "service" table for every car of the user_id=2 ordered by s_timestamp DESC

ORDER BY s_timestamp LIMIT 2 DESC

I try this query but gives me all the rows from "service"

SELECT ctu.user_id, c.car_id, c.plate, c.km, s.car_plate, s.s_timestamp, s.price
FROM cars_to_users ctu 
LEFT JOIN cars c ON  ctu.car_id = c.car_id
LEFT JOIN service s ON c.plate = s.car_plate 
WHERE ctu.user_id = '2' 
AND c.status = 1

If I add "GROUP BY c.car_id" I only get 1 row per car and not 2 I want

I try many queries but not get what I want.

A thing to keep in mind is that the table "service" has more than 9 million rows and more data than the example, and grows up.

Answer 1

This answer is quite complicated. And I'm not sure how well it will perform on your database.

SELECT ctu.user_id, c.car_id, c.plate, c.km, s.car_plate, s.s_timestamp, s.price
FROM cars_to_users ctu 
LEFT JOIN cars c ON  ctu.car_id = c.car_id
LEFT JOIN service s ON c.plate = s.car_plate
JOIN 
(
  SELECT service.car_plate,max(service.s_timestamp) as s_timestamp
  FROM service
  JOIN 
  (
    SELECT car_plate, max(s_timestamp) as s_timestamp FROM service GROUP BY car_plate
  ) as max_timestamp ON max_timestamp.car_plate = service.car_plate AND service.s_timestamp < max_timestamp.s_timestamp
  GROUP BY service.car_plate
) as max_2_timestamp ON s.car_plate = max_2_timestamp.car_plate AND s.s_timestamp >= max_2_timestamp.s_timestamp
WHERE ctu.user_id = '2' 
AND c.status = 1
ORDER BY s_timestamp DESC

I guess you can put the 2 sub queries in a temp table first like this

DROP TABLE IF EXISTS max_timestamp;
DROP TABLE IF EXISTS max_2_timestamp;

CREATE TEMPORARY table max_timestamp SELECT car_plate, max(s_timestamp) as s_timestamp FROM service GROUP BY car_plate;

CREATE TEMPORARY table max_2_timestamp
(
  SELECT service.car_plate,max(service.s_timestamp) as s_timestamp
  FROM service
  JOIN max_timestamp ON max_timestamp.car_plate = service.car_plate AND service.s_timestamp < max_timestamp.s_timestamp
  GROUP BY service.car_plate
);

SELECT ctu.user_id, c.car_id, c.plate, c.km, s.car_plate, s.s_timestamp, s.price
FROM cars_to_users ctu 
LEFT JOIN cars c ON  ctu.car_id = c.car_id
LEFT JOIN service s ON c.plate = s.car_plate
JOIN max_2_timestamp ON s.car_plate = max_2_timestamp.car_plate AND s.s_timestamp >= max_2_timestamp.s_timestamp
WHERE ctu.user_id = '2' 
AND c.status = 1
ORDER BY s_timestamp DESC;

EDIT: Another Alternative

You will only have one query but I can't check if it's efficient enough in your system.

Let's create the function that will retrieve the 2nd most recent s_timestamp for each car_plate in Service

CREATE FUNCTION LatestService (car_plate varchar(10))
RETURNS int(10)
RETURN 
(SELECT s_timestamp
FROM service s
WHERE s.car_plate=`car_plate`
ORDER BY s.s_timestamp desc
LIMIT 1,1);

Then you can perform the query using the function.

SELECT ctu.user_id, c.car_id, c.plate, c.km, s.car_plate, s.s_timestamp, s.price
FROM cars_to_users ctu 
LEFT JOIN cars c ON  ctu.car_id = c.car_id
LEFT JOIN service s ON c.plate = s.car_plate 
WHERE ctu.user_id = '2' 
AND c.status = 1
AND s.s_timestamp >= LatestService(s.car_plate);