Help writing a query that select the min column in a group by

Question

I have two simple table

CREATE TABLE user (
  id INT NOT NULL AUTO_INCREMENT,
  nickname varchar(35) NOT NULL, 
  PRIMARY KEY (id),
);

CREATE TABLE user_service (
  id INT NOT NULL AUTO_INCREMENT,
  user_id INT NOT NULL,
  service_type VARCHAR(10) NOT NULL,
  price numeric(15,2) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_service (user_id, service_type),
  FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
);

I'm trying to retrieve in ONE query a list of user that provides certain services. The query should return the user and the lowest price of its services

Here is the query I wrote, as you can see i have wrote a sub select and I was wondering if this is a good way of doing things? Or is there a more efficient way?

SELECT
    u.id,
    u.nickname,
    (select MIN(price) from user_service where user_id=u.id order by price desc) as minPrice
    FROM
      user u
      LEFT JOIN user_service us ON us.user_id = u.id
    WHERE us.service_type IN ('TYPE1','TYP2')
   group by u.id;

score 1 · Accepted Answer · answered Dec 04 '15 at 15:57

You can try this:

SELECT
    u.id,
    u.nickname,
    t.minPrice
    FROM
      user u
      LEFT JOIN user_service us ON us.user_id = u.id
      INNER JOIN (
    SELECT user_id, MIN(price) minPrice
        FROM user_service
        GROUP BY user_id    
    ) t ON t.user_id = u.id AND us.price = t.minPrice
    WHERE us.service_type IN ('TYPE1','TYPE2') and u.id=1
   group by u.id;

Help writing a query that select the min column in a group by

1 Answers1