replacing several nested queries and UNION ALLs with one query

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

replacing several nested queries and UNION ALLs with one query

Puneet Kishor-2
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3

On 20 Feb 2012, at 1:37am, Puneet Kishor <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: replacing several nested queries and UNION ALLs with onequery

Igor Tandetnik
In reply to this post by Puneet Kishor-2
Puneet Kishor <[hidden email]> 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
>
> Right now I am doing a bunch of UNION ALLs of separate queries, but am wondering if there is a better way.

select (case
    when a1 != '' AND a2 != '' AND a1 = a2 then a1
    when b1 != '' AND b2 != '' AND b1 = b2 then b1
    when c1 != '' AND c2 != '' AND c1 = c2 then c1
    else 'none' end) as foo
from t;

--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Gerry Snyder-4
In reply to this post by Puneet Kishor-2
a2 != '' seems redundant when a1 = a2 and a1 != ''
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users