mysql - Problem with LIMIT & IN/ALL/ANY/SOME subquery

Question

I have this query:

SELECT count(cp.CxID) as intSmokers 
FROM CustPrimarySmoking cp 
JOIN Customer c ON cp.CxID = c.CustomerID 
WHERE 
cp.CxID IN (SELECT CxID FROM CustPrimarySmoking WHERE CxID = cp.CxID LIMIT 1, 9999)

The idea being that the count will be based on the results of the nested query which retrieves all the records for that customer EXCEPT the first record.

HOWEVER, I get this error, which I think is pretty terminal:

1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Does anyone know of any other way of doing this?

Thanks

Answer 1

This is how you need to proceed. See the example that I've worked out.

mysql> select * from test;
+------+-------+
| id   | name  |
+------+-------+
|    1 | name1 |
|    2 | name2 |
|    3 | name3 |
|    4 | name4 |
+------+-------+
4 rows in set (0.00 sec)

mysql> select * from test1;
+------+------+--------+
| id   | tid  | name2  |
+------+------+--------+
|    1 |    2 | name11 |
|    2 |    3 | name12 |
|    3 |    4 | name13 |
+------+------+--------+
3 rows in set (0.00 sec)

mysql> select
    ->  t1.name
    -> from
    ->  test t1
    -> join
    ->  test1 t2 on t2.tid = t1.id
    -> join
    ->  (select id from test where id <4 limit 3) as tempt on tempt.id = t1.id;
+-------+
| name  |
+-------+
| name2 |
| name3 |
+-------+
2 rows in set (0.00 sec)

Hope this helps.