*The goal: Given a one-to-many relationship between tables A and B, get output from a single query where each row of output has a bunch of stuff from A and a bunch of stuff from exactly one of the possibly-many-matched-rows in B.*
I’ve read, and played, and played, and read, and while I didn’t think I was particularly stupid before starting this, I’m beginning to wonder… I also know this is supposedly well-trodden ground, but ...well, see above about being stupid. I can't find anything clear enough to get past the hump. Let’s say for the sake of argument that I’ve got a table of users and an ordered table of their classes: create table usr (id int, name varchar(255));insert into usr values (1, 'bill');insert into usr values (2, 'mike'); create table classes (user_id int, seq int, class varchar(255));insert into classes values (1, 1, 'math');insert into classes values (1, 2, 'reading');insert into classes values (2, 1, 'english');insert into classes values (2, 2, 'gym'); What I want is a listing of users and their last class of the day (as denoted by the highest seq value). 1 Bill reading 2 Mike gym The SQL I have for this isn’t exactly straightforward; there may be better ways, but I don’t want to do multiple queries. Here’s one way. select u_top.*, c_top.class from usr u_top, classes c_topwhere c_top.seq = (select max(seq) from classes where classes.user_id = u_top.id and u_top.id = c_top.user_id); When I try to reproduce that in the obvious way using sequel, it all fails. ds = DB[:usr.as(:u_top), :classes.as(:c_top)] ds.select_all!(:u_top) ds.select_append!(:c_top__class) inner = DB[:miu01__classes.as(:c_inner)] inner.select!(Sequel.function(:max, :c_inner__seq)) inner.where!(:c_inner__user_id => :c_top__user_id, :c_top__user_id => :u_top__id) ds.where!(:c_top__seq => inner) ds.all #=> [] But the kicker is that when I cut-and-paste the result of ds.sql into a command shell, I get the expected output. I’m sure I’m not properly aliasing the tables in some way and the underlying logic engine probably needs me to do an explicit join or graph or something, but I can’t figure it out to save my soul. Thanks for any advice… -Bill- -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
