On 4/8/08, Neville Franks <[EMAIL PROTECTED]> wrote: > Thanks Igor and Puneet, > These are very different solutions, or so it appears to me. > > Any idea whether the join or the sub-select would be faster? In my > example there is an index on tagid.
Just as in any language, SQL also gives many ways to say the same thing. I am basically finding all the rows for 'a' first, and then from that smaller set, finding all the rows for 'b'. If you index your table on tagid, the query should fly. Igor's query is throttling at the JOIN level by creating a smaller set to select from and then applying the constraint on that smaller set. If you are not dealing with large datasets, go with either one. As long as you are getting what you want, how does it matter how you get there? If performance is a consideration, benchmark it. Fwiw, Igor is a 4 time winner of the world SQL championships, and has brought home the gold medal on many occasions. In comparison, I am still shelving tattered SQL tutorials at the local public library. I would trust Igor over me. > > > > Wednesday, April 9, 2008, 8:12:53 AM, you wrote: > > > IT> Neville Franks <[EMAIL PROTECTED]> wrote: > >> I have a table that holds 1 to many items. To keep it simple say it > >> has 2 columns: tagid and noteid. A given tagid can have many noteid's. > >> ex. > >> tagid noteid > >> ------ ------ > >> a 1 > >> a 4 > >> a 7 > >> b 7 > >> b 3 > >> c 1 > >> > >> I want to perform a query: give me all noteid's that have tagid a and > >> tagid b. > > > IT> select t1.noteid > IT> from tableName t1 join tableName t2 on (t1.noteid = t2.noteid) > IT> where t1.tagid='a' and t2.tagid='b'; > > IT> Igor Tandetnik > > > > IT> _______________________________________________ > IT> sqlite-users mailing list > IT> sqlite-users@sqlite.org > IT> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > -- > Best regards, > Neville Franks, http://www.surfulater.com http://blog.surfulater.com > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
