Apply a limit on joining table in mysql

Question

I have two tables users and queries. users can post multiple queries.

MySQL Table Schema :-

CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `email` varchar(100) NOT NULL,
  `password` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `queries` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `address` text,
  `city` varchar(100) NOT NULL,
  `state` varchar(100) NOT NULL,
  `zipcode` int(11) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I want to apply limit on it that query fetch 5 queries from queries table for each user.

How should I write query for this?

SELECT * FROM `queries` as qry left join `users` as usr on usr.id = qry.user_id limit 5 =====You can also use order by clause — Sunil Pachlangia, Sep 04 '15 at 06:50

score 2 · Accepted Answer · edited Sep 04 '15 at 07:13

try it-

SELECT x.*
FROM (SELECT t.user_id,t.id,
               CASE 
                 WHEN @category != t.user_id THEN @rownum := 1 
                 ELSE @rownum := @rownum + 1 
               END AS rank,
               @category := t.user_id AS var_category
          FROM queries t
          JOIN (SELECT @rownum := NULL, @category := '') r     
      ORDER BY t.user_id,t.id) X
      WHERE x.rank<=5

Apply a limit on joining table in mysql

1 Answers1