CODEWITHSUNDEEP
mysqlleft-join
Antoine C.
Antoine C.

Reputation: 3952

Avoid using a subquery in a table join

In a MySQL 5.7 database, I have the following User table:

Name Id
David 1
Frank 2

And the following Order table:

Id Price UserId
1 55 1
2 68 1
3 50 1
4 10 2

For every user, I want to select the price of the order with the biggest ID.

I can use the following query which adds additional complexity due to the nested subquery :

SELECT
 User.Name,
 last_user_order.Price
FROM User
LEFT JOIN (
   SELECT Price, UserId FROM Order
   ORDER BY Id DESC LIMIT 1
) AS last_user_order ON last_user_order.UserId = User.Id

There exist many questions here where the column to be selected is the same than the one being ordered. Hence, it is possible to use MAX in the first SELECT statement to avoid a subquery. Is it possible to avoid a subquery in my case?

Upvotes: 0

Views: 40

Answers (2)

Caius Jard
Caius Jard

Reputation: 74605

For every user, I want to select the price of the order with the biggest ID.

That looks like:

SELECT
  u.*,
  o.Price,
FROM 
  User u
  INNER JOIN Order o ON u.ID = o.UserID
  INNER JOIN 
  (
    SELECT MAX(ID) as OrderID FROM Order GROUP BY UserId
  ) maxO ON o.Id = maxO.OrderId

Upvotes: 1

Akina
Akina

Reputation: 42632

SELECT User.Name,
       ( SELECT Order.Price
         FROM Order
         WHERE Order.UserId = User.Id
         ORDER BY Order.Id DESC LIMIT 1 ) LastPrice
FROM User;

Upvotes: 1

Related Questions