CODEWITHSUNDEEP
sqlsqlite
iggy
iggy

Reputation: 1743

sql union query with complex subueries in sqlite

I need to do a union of results of the two complex subqueries. I have the following query which works in sqlite

select * from (select 
               objectid from osm where tag = 'b' 
                     union 
               select 
               objectid from osm where tag = 'a');

I would like to modify it to be as follows (simplified):

select * from (select objectid from osm where tag = 'b' limit 10 
               union 
               select objectid from osm where tag = 'a' limit 10);

which is not valid in sqlite. So what I tried to do is

select * from 
    (select objectid from osm where tag = 'b' limit 10) as b, 
    (select objectid from osm where tag = 'a' limit 10) as a, 
    osm
where 
    osm.objectid in (a union b);

but this is again not valid. I know I could do 

 osm.objectid in a OR osm.objectid in b;

but for some technical reasons I can't use OR. Is there any way to do it without an OR?

Upvotes: 1

Views: 2087

Answers (2)

CL.
CL.

Reputation: 180162

LIMIT applies to the entire query, so you have to use a separate layer of subqueries to be able to UNION LIMITed queries:

select * from
  (select * from (select objectid from osm where tag = 'b' limit 10)
   union 
   select * from (select objectid from osm where tag = 'a' limit 10));

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270371

Here is a trick that I think lets you do what you want:

select *
from osm
where objectid in (select objectid from osm where tab = 'b' limit 10) or
      objectid in (select objectid from osm where tab = 'b' limit 10);

Upvotes: 0

Related Questions