Hi all, I'm trying to show pages of results in a web app, 30 items per page. But my main query is a join, and I can't use a simple "limit" clause to return the 30 items (with the desired offset), because the join is between two tables with a one-to-many relationship, and I want 30 of the objects in the first table, not 30 rows. For example, here are some simplified, with the usual one-to-many relation...
CREATE TABLE states ( id int primary key, name text ); CREATE TABLE cities ( id int primary key, state int, name text ) With some sample data, my query looks like this: mysql> select * from states, cities where states.id = cities.state; +----+----------------+----+-------+--------------+ | id | name | id | state | name | +----+----------------+----+-------+--------------+ | 1 | North Carolina | 1 | 1 | Durham | | 1 | North Carolina | 2 | 1 | Raleigh | | 1 | North Carolina | 3 | 1 | Chapel Hill | | 2 | California | 4 | 2 | Ventura | | 2 | California | 5 | 2 | Belmont | | 3 | Connecticut | 6 | 3 | Hartford | | 3 | Connecticut | 7 | 3 | Wethersfield | +----+----------------+----+-------+--------------+ 7 rows in set (0.00 sec) Now, I want the first 2 *states*, which would be the first 5 rows? Is there a good way to do that in one query? A subselect like the following doesn't work in my version of MySQL. mysql> select * from states, cities where states.id = cities.state and state.id in (select id from state limit 2); ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' mysql> select version(); +---------------------+ | version() | +---------------------+ | 4.1.21-community-nt | +---------------------+ Something with "distinct" or "group by" perhaps? thanks, Rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
