Re: [sqlite] SELECTing from another SELECT

[jose isaias cabrera]

John McKown wrote...

On Thu, Jan 23, 2014 at 1:11 PM, jose isaias cabrera
<cabr...@wrc.xerox.com>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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users