On 8 Feb 2014, at 10:03, Stephan Beal <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
