CODEWITHSUNDEEP
mysqlsqlmariadb
user3247608
user3247608

Reputation: 593

how to use limits in IN clause sql

When running the query

select BookID FROM Books_Collection WHERE CollectionID=22 AND BookID IN (SELECT BookID FROM Books LIMIT 0,10);

I get the error:

ERROR 1235 (42000): This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

How to use limits with the IN clause? If you can't can you show me how to do this without an IN clause.

Upvotes: 0

Views: 738

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Try doing this using a join instead:

SELECT bc.BookID
FROM Books_Collection bc JOIN
     (SELECT DISTINCT BookID
      FROM Books
      LIMIT 0,10
     ) b
     ON bc.BookId = b.BookId
WHERE bc.CollectionID = 22;

Some notes. First, using limit without an order by will give you an arbitrary 10 rows. They could change from one execution to another. You should be using an explicit order by.

Second, assuming that BookId is a valid foreign key, then you may not need to join to Books. You might just want:

SELECT DISTINCT bc.BookID
FROM Books_Collection bc 
WHERE bc.CollectionID = 22
LIMIT 0, 10;

Upvotes: 2

Related Questions