How about something like this:
select * from states, cities,
(select id from state limit 2) as stlimit
where states.id = cities.state and
state.id=stlimit.id
I tried it in 4.1 and it works.
----- Original Message -----
From: "Rob Nikander" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Wednesday, August 30, 2006 3:09 PM
Subject: limit clause on join results
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
