SELECT query with LIMIT from database column on MySQL

Question

I have 2 tables:

user          messages
+---------+   +----------------+
|id|credit|   |id|user_id| ... |
+---------+   +----------------+
|01| 05   |   |01| 01    | ... |
|02| 01   |   |02| 01    | ... |
|03| 00   |   |03| 01    | ... |
+---------+   |04| 02    | ... |
              |05| 02    | ... |
              |06| 03    | ... |
              +----------------+

I need to select n messages from messages where n=user.credit. The query I would like to execute a query like:

SELECT * 
FROM user u JOIN messages m ON u.id = m.user_id LIMIT u.credit 
WHERE user in ...;

And should return rows 01, 02, 03, 04 but not 05, 06.

I know I can't write LIMIT u.credit but want that behavior, limit the number of messages fetched for each user in function of the value of the credits they have (user 01 whit limit of 5, user 02 with limit of 1 and so on) and I want to fetch all the messages all at once to avoid having to do a COUNT and then a SELECT for each user.

I'm using MySQL with InnoDB

You can only specify a constant literal for LIMIT, not an expression. — Salman Arshad
– Salman Arshad, Commented Jan 9, 2013 at 17:46
Directly in mysql, your best bet is to write a stored procedure that selects the u.credit value into a variable, then builds a query-string using that value as the limit and then having the procedure execute that query. — newfurniturey
– newfurniturey, Commented Jan 9, 2013 at 17:48
@SalmanA I know, I'm looking for a workaround, but avoiding making multiple queries. — ButterDog
– ButterDog, Commented Jan 9, 2013 at 17:48

Muhammad Raheel · Accepted Answer · 2013-01-09 18:08:28Z

4

This is a solution in plain SQL, without using variables:

select user.*, messages.*
from user inner join messages
     on user.id=messages.user_id
where (select count(*) from messages m
       where m.user_id=user.id
       and m.id<messages.id)<user.credit

Demo

edited Jan 9, 2013 at 18:08

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

answered Jan 9, 2013 at 17:55

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Muhammad Raheel · Accepted Answer · 2013-01-09 18:10:02Z

1

I'm afraid this is going to be slow:

SELECT u.*
     , m.*
FROM 
    user AS u 
  JOIN
    ( SELECT m1.*, COUNT(*) AS cnt
      FROM messages m1
        JOIN messages m2
          ON  m2.user_id = m1.user_id 
          AND m2.id <= m1.id
      GROUP BY m1.user_id, m1.id 
    ) m 
      ON  m.user_id = u.id 
      AND m.cnt <= u.credit ;

Demo

edited Jan 9, 2013 at 18:10

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

answered Jan 9, 2013 at 18:07

ypercubeᵀᴹ

116k19 gold badges181 silver badges248 bronze badges

Comments

Marlin Pierce · Accepted Answer · 2013-01-09 18:36:22Z

0

There is a way to do that in SQL if you are determined to.

SELECT u.* 
FROM user u JOIN messages m ON u.id = m.user_id
  inner join user u2 on u2.id <= u.id
WHERE user in ...
GROUP BY u.id
HAVING count(u2.id) < u.credit

edited Jan 9, 2013 at 18:36

answered Jan 9, 2013 at 17:54

Marlin Pierce

10.1k5 gold badges33 silver badges56 bronze badges

2 Comments

ButterDog Over a year ago

It seems good, but MySQL does not allow expressions on the LIMIT clause

Marlin Pierce Over a year ago

I see that it should be <= instead of <. I'll edit my answer.

Collectives™ on Stack Overflow

SELECT query with LIMIT from database column on MySQL

3 Answers 3

Demo

Comments

Demo

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related