On 8 Feb 2014, at 10:03, Stephan Beal <sgb...@googlemail.com> wrote:
> i am trying like mad to, but can't seem formulate a query with 2 version > number inputs (1 and 2 in this case) and creates a result set with these > columns: > > - name. must include all names across both versions > - status: -1 if in version 1 but not v2, 0 if in both, 1 if in v2 but not > v1. Only tried on your example dataset, but try this: CREATE VIEW answer as select name, count(v2) - count(v1) as result from (select a.name as name, b.name as v1, c.name as v2 from v as a left join (select name from v where vid = 1) as b on a.name = b.name left join (select name from v where vid = 2) as c on a.name = c.name) group by name order by result asc; Thanks, Kev _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users