Is there any alternative way to write this t-sql query?

Question

I have these 3 tables:

DB Diagram

For each car I need to visualize the data about the last (most recent) reservation:

the car model (Model);
the user who reserved the car (Username);
when it was reserved (ReservedOn);
when it was returned (ReservedUntil).

If there is no reservation for a given car, I have to show only the car model. Other fields must be empty.

I wrote the following query:

SELECT 
  Reservations.CarId, 
  res.MostRecent, 
  Reservations.UserId, 
  Reservations.ReservedOn, 
  Reservations.ReservedUntil 
FROM 
  Reservations 
  JOIN (
    Select 
      Reservations.CarId, 
      MAX(Reservations.ReservedOn) AS 'MostRecent' 
    FROM 
      Reservations 
    GROUP BY 
      Reservations.CarId
  ) AS res ON res.carId = Reservations.CarId 
  AND res.MostRecent = Reservations.ReservedOn

Expected something like this

This first one works but I got stuck to obtain the result that I need. How could I write complete the query?

Could you provide some sample data and expect result? – D-Shih Aug 03 '19 at 13:37 — D-Shih, Aug 03 '19 at 13:37
@D-Shih Hi, I edited my post to show the expected result. – dodido Aug 03 '19 at 14:15 — dodido, Aug 03 '19 at 14:15

forpas · Answer 1 · 2019-08-03T15:10:24.697

0

With not exists:

select r.* from reservations r
where not exists (
  select 1 from reservations  
  where carid = r.carid and reservedon > r.reservedon
)

You can create a CTE with the above code and join it to the other tables:

with cte as (
    select r.* from reservations r
    where not exists (
      select 1 from reservations  
      where carid = r.carid and reservedon > r.reservedon
    )
)
select c.carid, c.model, u.username, cte.reservedon, cte.reserveduntil
from cars c
left join cte on c.carid = cte.carid 
left join users u on u.userid = cte.userid

If you don't want to use a CTE:

select c.carid, c.model, u.username, t.reservedon, t.reserveduntil
from cars c
left join (
    select r.* from reservations r
    where not exists (
      select 1 from reservations  
      where carid = r.carid and reservedon > r.reservedon
    )
) t on c.carid = t.carid 
left join users u on u.userid = t.userid

edited Aug 03 '19 at 15:10

answered Aug 03 '19 at 13:40

forpas

160,666
10
38
76

Hi @forpas, thank you for your answer. Is there any way to complete my query without creating a CTE? – dodido Aug 03 '19 at 14:16
So the reservedon column and the reserveduntil one are related to the most recent reservation. Looks like I get 2 more results than expected, I'll check again. Thank you! So the query that I wrote was not correct? – dodido Aug 03 '19 at 14:54
The code checks only for reservedon to be the latest. – forpas Aug 03 '19 at 14:57
Is there a case that in the table reservations the same carid can have more thsn 1 rows with the same reservedon date? – forpas Aug 03 '19 at 14:58
Yes. If you have two identical cars, they can be reserved the same day from different users. So, my idea to solve it (the subquery I wrote) isn't correct? EDIT: imagine a 'numbersOfCars' column in the cars table. – dodido Aug 03 '19 at 15:05
When you say *two identical cars* do you mean that they have the same carid? – forpas Aug 03 '19 at 15:09
I edited the code to include the carid in the results. – forpas Aug 03 '19 at 15:10
Yes, I mean they have the same ID but different 'numbersOf' value. Ok, so my 'group by' was wrong I guess. Is there any suggestion / link that can help me to approach this kind of queries? – dodido Aug 03 '19 at 15:23
Where is this numbersOf value? If it is unique for each car It should be the primary key in the table cars and you should store this in the table reservations instead of carid. – forpas Aug 03 '19 at 15:27
It is not unique. It is in the 'cars' table just to specify how many cars (same model) are there. – dodido Aug 03 '19 at 15:34
No. Two car models can have the same numbersof but have different ID. – dodido Aug 03 '19 at 16:48
Sorry, it comes from a series of exercises and probably I wasn't clear enough in my description. Nevertheless, your answer is helping me. – dodido Aug 04 '19 at 09:01

Vladimir Baranov · Accepted Answer · 2019-08-03T14:39:25.320

0

It looks like a classic top-n-per-group problem.

One way to do it is to use OUTER APPLY. It is a correlated subquery (lateral join), which returns the latest Reservation for each row in the Cars table. If such reservation doesn't exist for a certain car, there will be nulls.

If you create an index for Reservations table on (CarID, ReservedOn DESC), this query should be more efficient than self-join.

SELECT
    Cars.CarID
    ,Cars.Model
    ,A.ReservedOn
    ,A.ReservedUntil
    ,A.UserName
FROM
    Cars
    OUTER APPLY
    (
        SELECT TOP(1)
            Reservations.ReservedOn
            ,Reservations.ReservedUntil
            ,Users.UserName
        FROM
            Reservations
            INNER JOIN Users ON Users.UserId = Reservations.UserId
        WHERE
            Reservations.CarID = Cars.CarID
        ORDER BY
            Reservations.ReservedOn DESC
    ) AS A
;

For other approaches to this common problem see Get top 1 row of each group

and Retrieving n rows per group

edited Aug 03 '19 at 14:39

answered Aug 03 '19 at 14:34

Vladimir Baranov

31,799
5
53
90

Hi Vladimir, thank you for your answer. I can't use an OUTER APPLY. It is something like an exercise and I have some limitations. As you can read in my post, my first idea was to group reservations by the car ID and find the most recent one. Then, I join to it the reservations table. Now I have all the informations I need except... I have to add car models that have not a reservation entry in the reservations table. It is here that I got stuck. Any suggestions? – dodido Aug 03 '19 at 15:02
@dodido, Well, simply left join `Cars` with your whole query. Your query returns one row per `CarID` that exists in the `Reservations`. Fine. `SELECT ... FROM Cars LEFT JOIN (Your query) AS T ON Cars.CarID = T.CarID`. It is not efficient, but it will work. – Vladimir Baranov Aug 03 '19 at 15:15
Hi Vladimir, you mean: [example](https://i.ibb.co/s5gGPwk/query.jpg)? I get: the column 'CarId' was specified multiple times for 't'. – dodido Aug 04 '19 at 09:15
Ok, this one should work: [solution] (https://i.ibb.co/ssD8nv9/query-solution.jpg). – dodido Aug 04 '19 at 09:49
@dodido, it is great that you managed to figure it out yourself. – Vladimir Baranov Aug 04 '19 at 11:05

Is there any alternative way to write this t-sql query?

2 Answers2