Multiple SELECTs in one call

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

Multiple SELECTs in one call

Jose Isaias Cabrera-4

Greetings.

I need some help from you gurus to have multiple selects, but the sequence is important.  For example,

create table t (a, b, c, d, e);
insert into t values (1,2,3,4,5);
insert into t values (2,2,3,4,5);
insert into t values (3,3,3,3,3);
insert into t values (4,1,1,1,1);
insert into t values (5,1,1,2,2);
insert into t values (6,2,3,2,2);

what I want to do is to do a select that first chooses all items where e != 1, and then the result of that select be used to create more selects based on other criteria. For example,

SELECT * from t WHERE a IN (SELECT a from t where e != 1);

But I want to use the result of (SELECT a from t where e != 1); to run another select (SELECT a from t where d > 3); and then, one more select (SELECT a from t where c != 1 AND b != 1); and the final select would be "SELECT * from t WHERE a IN" which would have the result.  I hope someone understands. :-)  Thanks for the help.

josé

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

Re: Multiple SELECTs in one call

Simon Slavin-3
On 7 Feb 2019, at 4:21am, Jose Isaias Cabrera <[hidden email]> wrote:

>  want to use the result of (SELECT a from t where e != 1); to run another select (SELECT a from t where d > 3); and then, one more select (SELECT a from t where c != 1 AND b != 1);

How are these related to each other ?

Do you want one big result which has the rows returned by the first SELECT, and also the rows returned by the second SELECT, and also the rows returned by the third SELECT ?

If so, you use UNION:

<https://www.w3schools.com/sql/sql_union.asp>

Or did I get it wrong ?

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

Re: Multiple SELECTs in one call

Keith Medcalf
In reply to this post by Jose Isaias Cabrera-4

You mean something like

select *
  from t
 where a in (select a from t where e != 1
             union
             select a from t where d > 3
             union
             SELECT a from t where c != 1 AND b != 1);

or more succinctly:

select *
  from t
 where a in (select a
              from t
             where e != 1
                or d > 3
                or (    c != 1
                    and b != 1));

or do you mean:

select *
  from t
 where a in (select a from t where e != 1
             intersect
             select a from t where d > 3
             intersect
             SELECT a from t where c != 1 AND b != 1);

or more succinctly:

select *
  from t
 where a in (select a
              from t
             where e != 1
               and d > 3
               and c != 1
               and b != 1);

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Jose Isaias Cabrera
>Sent: Wednesday, 6 February, 2019 21:22
>To: [hidden email]
>Subject: [sqlite] Multiple SELECTs in one call
>
>
>Greetings.
>
>I need some help from you gurus to have multiple selects, but the
>sequence is important.  For example,
>
>create table t (a, b, c, d, e);
>insert into t values (1,2,3,4,5);
>insert into t values (2,2,3,4,5);
>insert into t values (3,3,3,3,3);
>insert into t values (4,1,1,1,1);
>insert into t values (5,1,1,2,2);
>insert into t values (6,2,3,2,2);
>
>what I want to do is to do a select that first chooses all items
>where e != 1, and then the result of that select be used to create
>more selects based on other criteria. For example,
>
>SELECT * from t WHERE a IN (SELECT a from t where e != 1);
>
>But I want to use the result of (SELECT a from t where e != 1); to
>run another select (SELECT a from t where d > 3); and then, one more
>select (SELECT a from t where c != 1 AND b != 1); and the final
>select would be "SELECT * from t WHERE a IN" which would have the
>result.  I hope someone understands. :-)  Thanks for the help.
>
>josé
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: Multiple SELECTs in one call

Jose Isaias Cabrera-4
In reply to this post by Simon Slavin-3

They are related by a, but I see that Keith prepared a nice answer.  Thanks.  And yes, UNION is the key here.  I thought that it was to UNITE other tables, but I can see that I can use it with the same table.  Thanks.


________________________________
From: sqlite-users <[hidden email]> on behalf of Simon Slavin <[hidden email]>
Sent: Wednesday, February 6, 2019 11:30 PM
To: SQLite mailing list
Subject: Re: [sqlite] Multiple SELECTs in one call

On 7 Feb 2019, at 4:21am, Jose Isaias Cabrera <[hidden email]> wrote:

>  want to use the result of (SELECT a from t where e != 1); to run another select (SELECT a from t where d > 3); and then, one more select (SELECT a from t where c != 1 AND b != 1);

How are these related to each other ?

Do you want one big result which has the rows returned by the first SELECT, and also the rows returned by the second SELECT, and also the rows returned by the third SELECT ?

If so, you use UNION:

<https://www.w3schools.com/sql/sql_union.asp>

Or did I get it wrong ?

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

Re: Multiple SELECTs in one call

Jose Isaias Cabrera-4
In reply to this post by Keith Medcalf

Thanks, Keith.  Yep, exactly what I was looking for.  Gracias.

________________________________
From: sqlite-users <[hidden email]> on behalf of Keith Medcalf <[hidden email]>
Sent: Wednesday, February 6, 2019 11:37 PM
To: SQLite mailing list
Subject: Re: [sqlite] Multiple SELECTs in one call


You mean something like

select *
  from t
 where a in (select a from t where e != 1
             union
             select a from t where d > 3
             union
             SELECT a from t where c != 1 AND b != 1);

or more succinctly:

select *
  from t
 where a in (select a
              from t
             where e != 1
                or d > 3
                or (    c != 1
                    and b != 1));

or do you mean:

select *
  from t
 where a in (select a from t where e != 1
             intersect
             select a from t where d > 3
             intersect
             SELECT a from t where c != 1 AND b != 1);

or more succinctly:

select *
  from t
 where a in (select a
              from t
             where e != 1
               and d > 3
               and c != 1
               and b != 1);

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Jose Isaias Cabrera
>Sent: Wednesday, 6 February, 2019 21:22
>To: [hidden email]
>Subject: [sqlite] Multiple SELECTs in one call
>
>
>Greetings.
>
>I need some help from you gurus to have multiple selects, but the
>sequence is important.  For example,
>
>create table t (a, b, c, d, e);
>insert into t values (1,2,3,4,5);
>insert into t values (2,2,3,4,5);
>insert into t values (3,3,3,3,3);
>insert into t values (4,1,1,1,1);
>insert into t values (5,1,1,2,2);
>insert into t values (6,2,3,2,2);
>
>what I want to do is to do a select that first chooses all items
>where e != 1, and then the result of that select be used to create
>more selects based on other criteria. For example,
>
>SELECT * from t WHERE a IN (SELECT a from t where e != 1);
>
>But I want to use the result of (SELECT a from t where e != 1); to
>run another select (SELECT a from t where d > 3); and then, one more
>select (SELECT a from t where c != 1 AND b != 1); and the final
>select would be "SELECT * from t WHERE a IN" which would have the
>result.  I hope someone understands. :-)  Thanks for the help.
>
>josé
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users