Re: [sqlite] replacing several nested queries and UNION ALLs with one query
a2 != '' seems redundant when a1 = a2 and a1 != '' ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] replacing several nested queries and UNION ALLs with one query
On 20 Feb 2012, at 1:37am, Puneet Kishor wrote: > Say I have table like so > > CREATE TABLE t ( > a1, a2, > b1, b2, > c1, c2 > ); > > I want > > a1 AS foo WHERE a1 != '' AND a2 != '' AND a1 = a2 > > from the remaining > > b1 AS foo WHERE b1 != '' AND b2 != '' AND b1 = b2 > > from the remaining > > c1 AS foo WHERE c1 != '' AND c2 != '' AND c1 = c2 > > from the remaining > > 'none' AS foo You can use CASE WHEN leaving your data as it is: http://www.sqlite.org/lang_expr.html But I suggest you also look at coalesce() on this page: http://www.sqlite.org/lang_corefunc.html since it might give you a simpler and faster function which might help if your app runs too slow. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] replacing several nested queries and UNION ALLs with one query
Say I have table like so CREATE TABLE t ( a1, a2, b1, b2, c1, c2 ); I want a1 AS foo WHERE a1 != '' AND a2 != '' AND a1 = a2 from the remaining b1 AS foo WHERE b1 != '' AND b2 != '' AND b1 = b2 from the remaining c1 AS foo WHERE c1 != '' AND c2 != '' AND c1 = c2 from the remaining 'none' AS foo Right now I am doing a bunch of UNION ALLs of separate queries, but am wondering if there is a better way. Oh, in reality there are three more such pairs (d1, d2, e1, e2, f1, f2), but the above should be sufficient to develop a pattern. My current query SELECT a1 AS foo FROM t WHERE a1 != '' AND a2 != '' AND a1 = a2 UNION ALL SELECT b2 AS foo FROM ( SELECT b1, b2 FROM t WHERE a1 != '' OR a2 != '' OR a1 != a2 ) tmp WHERE tmp.b1 != '' AND tmp.b2 != AND tmp.b1 = tmp.b2 UNION ALL and so on... -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users