Try it like this...I created your tables and this at least ran: > select a.customer_id from > ( select customer_id from training.ratings where book_id in > ( select book_id from training.ratings where customer_id= 5 ) > order by customer_id > ) as a
The difference is that your first compares a complete result set to a complete result set, resulting in a...I dunno, a cartesian product I think, and I can see how this query could be written with a JOIN, which may be more efficient. But your sub-select makes a selection from a result set...treats the result set as the DB object you're selecting from. So it needs an alias...I think. See if this or something like it could work for you instead (it does run for me against your tables): SELECT a.customer_id,a.book_id,b.customer_id FROM training.ratings AS a INNER JOIN training.ratings AS b ON a.book_id = b.book_id WHERE b.customer_id = 5 On Dec 30, 5:44 pm, "[email protected]" <[email protected]> wrote: > Hi all, > > I'm having a trouble with H2 getting stuck on a sub-query. If I do > the following, it returns very quickly with my results: > > select customer_id from training.ratings where book_id in > ( select book_id from training.ratings where customer_id = 5 ) > order by customer_id > > However if I embed this query into another query, it hangs: > > select customer_id from > ( select customer_id from training.ratings where book_id in > ( select book_id from training.ratings where customer_id= 5 ) > order by customer_id > ) > > My tables are created with the following: > > create schema training > create table training.customers (id int primary key); > create table training.books (id int primary key, name varchar, > date date); > create table training.ratings (customer_id int not null, book_id int > not null, date date not null, rating real, primary key (customer_id, > book_id)); > > Any help appreciated, thanks. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/h2-database?hl=en -~----------~----~----~----~------~----~------~--~---
