what's the syntax for selecting several ids into a variable, and then using that in an IN

I'm unable to figure out how to do the following... I'm pretty sure it's possible but am getting bad synt开发者_Python百科ax errors...

 /*pseudo*/
set @ids = select id from table_a limit 1,10;
select * from table_b where table_a_id in (@ids);

I would just put the select in the in() but mySQL says it's not willing to do a subselect in an IN that has a limit.

Thanks in advance

you can do this:

select table_b.* 
from table_b
join (select id from table_a limit 1,10) As table_a
  on table_b.table_a_id = table_a.id

select * from table_b where table_a_id in (select id from @ids);

You should be able to do this by joining a derived table.

SELECT
  table_b.id,
  table_b.otherfield
FROM table_b
  JOIN (SELECT id FROM table_a LIMIT 1,10) a_limit ON table_b.table_a_id = a_limit.id