Re: [sqlite] replacing several nested queries and UNION ALLs with one query

[Gerry Snyder]

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

[Simon Slavin]

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

[Puneet Kishor]

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