SELECTing from another SELECT

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

SELECTing from another SELECT

jic

Greetings!

I have these tables A and B:
A
id,projid,a,b
1,1,'a','h'
2,2,'b','i'
3,3,'c','j'
4,4,'d','k'
5,5,'e','l'
...
...


B
id,projid,ptask
101,1,'a'
102,2,'b'
103,3,'a'
104,4,'b'
105,5,'a'
...
...

When I do this SELECT,

SELECT * FROM A WHERE projid = (SELECT projid FROM B WHERE ptask = 'a');

This only returns one record (record 1) where it should return all the
records with ptask = 'a'.  Will someone point me to the right syntax to get
this correct?  Thanks for the help.

josé

_______________________________________________
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: SELECTing from another SELECT

Igor Tandetnik-2
On 1/23/2014 2:11 PM, jose isaias cabrera wrote:
> SELECT * FROM A WHERE projid = (SELECT projid FROM B WHERE ptask = 'a');

You want

WHERE projid IN (SELECT ...)

IN operator accepts a set on the right; = operator only accepts a scalar
(which comes from the first row of the sub-select).
--
Igor Tandetnik

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

Re: SELECTing from another SELECT

jic

Igor Tandetnik wrote...

> On 1/23/2014 2:11 PM, jose isaias cabrera wrote:
>> SELECT * FROM A WHERE projid = (SELECT projid FROM B WHERE ptask = 'a');
>
> You want
>
> WHERE projid IN (SELECT ...)
>
> IN operator accepts a set on the right; = operator only accepts a scalar
> (which comes from the first row of the sub-select).

Thanks, Igor.  Someday, I want to be like you. :-)

josé

_______________________________________________
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: SELECTing from another SELECT

St. B.
In reply to this post by jic
SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask = 'a');

will probably fill the bill.

If I where to run your query, I would do a
select A.* from A inner join B on A.a = b.ProjId where b.ptask='a'

the join may optimize better than the in (select ...)



On Thu, Jan 23, 2014 at 8:11 PM, jose isaias cabrera
<[hidden email]>wrote:

>
> Greetings!
>
> I have these tables A and B:
> A
> id,projid,a,b
> 1,1,'a','h'
> 2,2,'b','i'
> 3,3,'c','j'
> 4,4,'d','k'
> 5,5,'e','l'
> ...
> ...
>
>
> B
> id,projid,ptask
> 101,1,'a'
> 102,2,'b'
> 103,3,'a'
> 104,4,'b'
> 105,5,'a'
> ...
> ...
>
> When I do this SELECT,
>
> SELECT * FROM A WHERE projid = (SELECT projid FROM B WHERE ptask = 'a');
>
> This only returns one record (record 1) where it should return all the
> records with ptask = 'a'.  Will someone point me to the right syntax to get
> this correct?  Thanks for the help.
>
> josé
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
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: SELECTing from another SELECT

jose isaias cabrera

St. B. wrote...

> SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask = 'a');
>
> will probably fill the bill.
>
> If I where to run your query, I would do a
> select A.* from A inner join B on A.a = b.ProjId where b.ptask='a'
>
> the join may optimize better than the in (select ...)

Thanks.  I will give this a try also.  As we say in the Spanish language:
Muchas gracias.

josé

_______________________________________________
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: SELECTing from another SELECT

John McKown
In reply to this post by jic
On Thu, Jan 23, 2014 at 1:11 PM, jose isaias cabrera
<[hidden email]>wrote:

>
> Greetings!
>
> I have these tables A and B:
> A
> id,projid,a,b
> 1,1,'a','h'
> 2,2,'b','i'
> 3,3,'c','j'
> 4,4,'d','k'
> 5,5,'e','l'
> ...
> ...
>
>
> B
> id,projid,ptask
> 101,1,'a'
> 102,2,'b'
> 103,3,'a'
> 104,4,'b'
> 105,5,'a'
> ...
> ...
>
> When I do this SELECT,
>
> SELECT * FROM A WHERE projid = (SELECT projid FROM B WHERE ptask = 'a');
>
> This only returns one record (record 1) where it should return all the
> records with ptask = 'a'.  Will someone point me to the right syntax to get
> this correct?  Thanks for the help.
>
> josé
>

No, it is working properly. The reason is that the SELECT from the B table
returns two values. The WHERE clause in the outer SELECT from the A table
only tests the projid from the first value returned, not "either" value
being equal. At Igor said, you should use the IN phrase instead of an equal
sign. That is:

SELECT * FROM A WHERE projid IN (SELECT projid FROM B WHERE ptask = 'a');

= is for comparing a single value. IN is used for comparing against a set
of values.

--
Wasn't there something about a PASCAL programmer knowing the value of
everything and the Wirth of nothing?

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

Re: SELECTing from another SELECT

jic

John McKown wrote...


> On Thu, Jan 23, 2014 at 1:11 PM, jose isaias cabrera
> <[hidden email]>wrote:
>
>>
>> Greetings!
>>
>> I have these tables A and B:
>> A
>> id,projid,a,b
>> 1,1,'a','h'
>> 2,2,'b','i'
>> 3,3,'c','j'
>> 4,4,'d','k'
>> 5,5,'e','l'
>> ...
>> ...
>>
>>
>> B
>> id,projid,ptask
>> 101,1,'a'
>> 102,2,'b'
>> 103,3,'a'
>> 104,4,'b'
>> 105,5,'a'
>> ...
>> ...
>>
>> When I do this SELECT,
>>
>> SELECT * FROM A WHERE projid = (SELECT projid FROM B WHERE ptask = 'a');
>>
>> This only returns one record (record 1) where it should return all the
>> records with ptask = 'a'.  Will someone point me to the right syntax to
>> get
>> this correct?  Thanks for the help.
>>
>> josé
>>
>
> No, it is working properly. The reason is that the SELECT from the B table
> returns two values. The WHERE clause in the outer SELECT from the A table
> only tests the projid from the first value returned, not "either" value
> being equal. At Igor said, you should use the IN phrase instead of an
> equal
> sign. That is:
>
> SELECT * FROM A WHERE projid IN (SELECT projid FROM B WHERE ptask = 'a');
>
> = is for comparing a single value. IN is used for comparing against a set
> of values.

Thanks, Archie. :-)
>
> --
> Wasn't there something about a PASCAL programmer knowing the value of
> everything and the Wirth of nothing?
>
> Maranatha! <><
> John McKown

Maranatha indeed!

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

Re: SELECTing from another SELECT

jic
In reply to this post by jic
St. B. wrote...

> SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask = 'a');
>
> will probably fill the bill.
>
> If I where to run your query, I would do a
> select A.* from A inner join B on A.a = b.ProjId where b.ptask='a'
>
> the join may optimize better than the in (select ...)

 Thanks.  I will give this a try also.  As we say in the Spanish language:
 Muchas gracias.

 josé

_______________________________________________
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: SELECTing from another SELECT

Igor Tandetnik-2
In reply to this post by St. B.
On 1/23/2014 2:26 PM, St. B. wrote:
> SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask = 'a');
>
> will probably fill the bill.
>
> If I where to run your query, I would do a
> select A.* from A inner join B on A.a = b.ProjId where b.ptask='a'

Careful - this will produce a different result if B has multiple rows
with the same ptask and projid. May or may not be a concern in the OP's
case.
--
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: SELECTing from another SELECT

James K. Lowden
In reply to this post by John McKown
On Thu, 23 Jan 2014 13:28:50 -0600
John McKown <[hidden email]> wrote:

> > SELECT * FROM A WHERE projid = (SELECT projid FROM B WHERE ptask =
> > 'a');
> >
> > This only returns one record (record 1) where it should return all
> > the records with ptask = 'a'.
>
> No, it is working properly. The reason is that the SELECT from the B
> table returns two values. The WHERE clause in the outer SELECT from
> the A table only tests the projid from the first value returned, not
> "either" value being equal.

I'm not so sure about "properly".  Silently providing an arbitrary
result may be expected if you know to look for it.  AFAIK it's not
documented and it's hard to defend logically.  

The correct SQL result would be a syntax error message indicating
comparison of a scalar to a non-scalar.  

--jkl
_______________________________________________
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: SELECTing from another SELECT

Hick Gunter
In reply to this post by jic
Use IN

Projid = (SELECT ...) takes only the first value returned from the subselect

-----Ursprüngliche Nachricht-----
Von: jose isaias cabrera [mailto:[hidden email]]
Gesendet: Donnerstag, 23. Jänner 2014 20:11
An: General Discussion of SQLite Database
Betreff: [sqlite] SELECTing from another SELECT


Greetings!

I have these tables A and B:
A
id,projid,a,b
1,1,'a','h'
2,2,'b','i'
3,3,'c','j'
4,4,'d','k'
5,5,'e','l'
...
...


B
id,projid,ptask
101,1,'a'
102,2,'b'
103,3,'a'
104,4,'b'
105,5,'a'
...
...

When I do this SELECT,

SELECT * FROM A WHERE projid = (SELECT projid FROM B WHERE ptask = 'a');

This only returns one record (record 1) where it should return all the records with ptask = 'a'.  Will someone point me to the right syntax to get this correct?  Thanks for the help.

josé

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


--------------------------------------------------------------------------
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: [hidden email]

This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation.
_______________________________________________
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: SELECTing from another SELECT

jose isaias cabrera
In reply to this post by Igor Tandetnik-2

Igor Tandetnik wrote...

> On 1/23/2014 2:26 PM, St. B. wrote:
>> SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask = 'a');
>>
>> will probably fill the bill.
>>
>> If I where to run your query, I would do a
>> select A.* from A inner join B on A.a = b.ProjId where b.ptask='a'
>
> Careful - this will produce a different result if B has multiple rows with
> the same ptask and projid. May or may not be a concern in the OP's case.

What would be the correct inner join syntax to provide the same output as
the original select?  I like that shorter syntax.  Will any of these will be
faster?  Thanks so much.

josé

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

Re: SELECTing from another SELECT

jic
In reply to this post by Igor Tandetnik-2

Igor Tandetnik wrote...

> On 1/23/2014 2:26 PM, St. B. wrote:
>> SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask = 'a');
>>
>> will probably fill the bill.
>>
>> If I where to run your query, I would do a
>> select A.* from A inner join B on A.a = b.ProjId where b.ptask='a'
>
> Careful - this will produce a different result if B has multiple rows with
> the same ptask and projid. May or may not be a concern in the OP's case.

What would be the correct inner join syntax to provide the same output as
the original select?  I like that shorter syntax.  Will any of these will be
faster?  Thanks so much.

josé

_______________________________________________
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: SELECTing from another SELECT

Igor Tandetnik-2
On 1/24/2014 9:28 AM, jose isaias cabrera wrote:

>
> Igor Tandetnik wrote...
>> On 1/23/2014 2:26 PM, St. B. wrote:
>>> SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask =
>>> 'a');
>>>
>>> will probably fill the bill.
>>>
>>> If I where to run your query, I would do a
>>> select A.* from A inner join B on A.a = b.ProjId where b.ptask='a'
>>
>> Careful - this will produce a different result if B has multiple rows
>> with the same ptask and projid. May or may not be a concern in the
>> OP's case.
>
> What would be the correct inner join syntax to provide the same output as
> the original select?  I like that shorter syntax.  Will any of these
> will be
> faster?  Thanks so much.

"SELECT DISTINCT ..." perhaps. But that would kill any alleged
performance improvement that a join may have over IN (if any;
personally, I'm not sure it's necessarily true that the join would
perform better in the first place).
--
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: SELECTing from another SELECT

jose isaias cabrera

"Igor Tandetnik" wrote on Friday, January 24, 2014 9:48 AM...


> On 1/24/2014 9:28 AM, jose isaias cabrera wrote:
>>
>> Igor Tandetnik wrote...
>>> On 1/23/2014 2:26 PM, St. B. wrote:
>>>> SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask =
>>>> 'a');
>>>>
>>>> will probably fill the bill.
>>>>
>>>> If I where to run your query, I would do a
>>>> select A.* from A inner join B on A.a = b.ProjId where b.ptask='a'
>>>
>>> Careful - this will produce a different result if B has multiple rows
>>> with the same ptask and projid. May or may not be a concern in the
>>> OP's case.
>>
>> What would be the correct inner join syntax to provide the same output as
>> the original select?  I like that shorter syntax.  Will any of these
>> will be
>> faster?  Thanks so much.
>
> "SELECT DISTINCT ..." perhaps. But that would kill any alleged performance
> improvement that a join may have over IN (if any; personally, I'm not sure
> it's necessarily true that the join would perform better in the first
> place).

You are right. Your original IN command is much faster.  It instantly
responds, while the INNER JOIN takes a few second before responding.
Thanks.

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

Re: SELECTing from another SELECT

jic
In reply to this post by Igor Tandetnik-2

"Igor Tandetnik" wrote on Friday, January 24, 2014 9:48 AM...


> On 1/24/2014 9:28 AM, jose isaias cabrera wrote:
>>
>> Igor Tandetnik wrote...
>>> On 1/23/2014 2:26 PM, St. B. wrote:
>>>> SELECT * FROM A WHERE projid in (SELECT projid FROM B WHERE ptask =
>>>> 'a');
>>>>
>>>> will probably fill the bill.
>>>>
>>>> If I where to run your query, I would do a
>>>> select A.* from A inner join B on A.a = b.ProjId where b.ptask='a'
>>>
>>> Careful - this will produce a different result if B has multiple rows
>>> with the same ptask and projid. May or may not be a concern in the
>>> OP's case.
>>
>> What would be the correct inner join syntax to provide the same output as
>> the original select?  I like that shorter syntax.  Will any of these
>> will be
>> faster?  Thanks so much.
>
> "SELECT DISTINCT ..." perhaps. But that would kill any alleged performance
> improvement that a join may have over IN (if any; personally, I'm not sure
> it's necessarily true that the join would perform better in the first
> place).

You are right, Igor. Your original IN command is much faster.  It instantly
responds, while the INNER JOIN takes a few second before responding.
Thanks.

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