Re: [sqlite] Selecting unique entries from one table with multiple columns forming a unique key
Understood. Thank you for discussing this with me. Your help is greatly appreciated. -- Rich On Thu, Dec 3, 2009 at 5:03 PM, Pavel Ivanov wrote: >> Can I ask a follow up question? "from foo, >> bar"... is this behaving like a join? I saw this before and didn't >> quite understand it, I just realized it permuted all rows of the each >> table together (which sounds like a join with no conditions). > > Yes, it is join. What you seem to talk about is a cartesian product > and this would be it if it wasn't WHERE clause. With WHERE clause it > is inner join - SQLite gets smaller of two tables (foo and bar) and > for each row in it scans another table for rows satisfying WHERE > condition. And this behavior is the same as for your query if your foo > table is smaller than bar. But in case if you create index on (col2, > col3) in biggest of the tables foo or bar then my query will work an > order of magnitude faster than yours. > > > Pavel > > On Thu, Dec 3, 2009 at 4:54 PM, Rich Rattanni wrote: >> Awesome! Thank you. Can I ask a follow up question? "from foo, >> bar"... is this behaving like a join? I saw this before and didn't >> quite understand it, I just realized it permuted all rows of the each >> table together (which sounds like a join with no conditions). >> >> Second, I came up with my own solution: >> >> select * from foo >> where col1 || col2 in (select col1 || col2 from bar) >> >> This also works, but from an efficiency standpoint I assume this is >> horrible since it would basically be a series of string compare >> operations. However, if the number of rows in each table is VERY >> LARGE (lets say 50,000) would my solution maybe outperform the first >> (on the surface seems like n^2 vs n*S where S is concat string length >> (which will always be < 50)). >> >> On Thu, Dec 3, 2009 at 4:39 PM, Pavel Ivanov wrote: >>> Maybe >>> >>> select distinct bar.* from foo, bar >>> where foo.col2 = bar.col2 and foo.col3 = bar.col3 >>> >>> It's not clear from your requirements written below whether you need >>> 'distinct' here or not but I've added it just in case... >>> >>> >>> Pavel >>> >>> On Thu, Dec 3, 2009 at 4:34 PM, Rich Rattanni wrote: Suppose I have the following two tables foo: 10 | A | A 20 | B | B 30 | C | C and bar: 1 | A | X 2 | B | C 3 | A | A 4 | C | A 5 | B | B I want to select all the rows in table bar where the second and third column match an entry found in foo (that is to say, I want my result to be 3 | A | A and 5 | B | B. My attempt of: select * from bar where col2 in (select col2 from foo where bar.col2 = col2) and col3 in (select col3 from foo where bar.col3 = col3) Does not work, and I understand that is should not (it returns 2 | B | C, 3 | A | A, 4 | C | A, 5 | B | B). Could someone give me a hand? -- Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting unique entries from one table with multiple columns forming a unique key
> Can I ask a follow up question? "from foo, > bar"... is this behaving like a join? I saw this before and didn't > quite understand it, I just realized it permuted all rows of the each > table together (which sounds like a join with no conditions). Yes, it is join. What you seem to talk about is a cartesian product and this would be it if it wasn't WHERE clause. With WHERE clause it is inner join - SQLite gets smaller of two tables (foo and bar) and for each row in it scans another table for rows satisfying WHERE condition. And this behavior is the same as for your query if your foo table is smaller than bar. But in case if you create index on (col2, col3) in biggest of the tables foo or bar then my query will work an order of magnitude faster than yours. Pavel On Thu, Dec 3, 2009 at 4:54 PM, Rich Rattanni wrote: > Awesome! Thank you. Can I ask a follow up question? "from foo, > bar"... is this behaving like a join? I saw this before and didn't > quite understand it, I just realized it permuted all rows of the each > table together (which sounds like a join with no conditions). > > Second, I came up with my own solution: > > select * from foo > where col1 || col2 in (select col1 || col2 from bar) > > This also works, but from an efficiency standpoint I assume this is > horrible since it would basically be a series of string compare > operations. However, if the number of rows in each table is VERY > LARGE (lets say 50,000) would my solution maybe outperform the first > (on the surface seems like n^2 vs n*S where S is concat string length > (which will always be < 50)). > > On Thu, Dec 3, 2009 at 4:39 PM, Pavel Ivanov wrote: >> Maybe >> >> select distinct bar.* from foo, bar >> where foo.col2 = bar.col2 and foo.col3 = bar.col3 >> >> It's not clear from your requirements written below whether you need >> 'distinct' here or not but I've added it just in case... >> >> >> Pavel >> >> On Thu, Dec 3, 2009 at 4:34 PM, Rich Rattanni wrote: >>> Suppose I have the following two tables >>> >>> foo: >>> 10 | A | A >>> 20 | B | B >>> 30 | C | C >>> >>> and >>> >>> bar: >>> 1 | A | X >>> 2 | B | C >>> 3 | A | A >>> 4 | C | A >>> 5 | B | B >>> >>> >>> I want to select all the rows in table bar where the second and third >>> column match an entry found in foo (that is to say, I want my result >>> to be 3 | A | A and 5 | B | B. >>> >>> My attempt of: >>> select * from bar >>> where col2 in (select col2 from foo where bar.col2 = col2) >>> and col3 in (select col3 from foo where bar.col3 = col3) >>> >>> Does not work, and I understand that is should not (it returns 2 | B | >>> C, 3 | A | A, 4 | C | A, 5 | B | B). >>> >>> Could someone give me a hand? >>> >>> -- >>> Rich >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting unique entries from one table with multiple columns forming a unique key
Awesome! Thank you. Can I ask a follow up question? "from foo, bar"... is this behaving like a join? I saw this before and didn't quite understand it, I just realized it permuted all rows of the each table together (which sounds like a join with no conditions). Second, I came up with my own solution: select * from foo where col1 || col2 in (select col1 || col2 from bar) This also works, but from an efficiency standpoint I assume this is horrible since it would basically be a series of string compare operations. However, if the number of rows in each table is VERY LARGE (lets say 50,000) would my solution maybe outperform the first (on the surface seems like n^2 vs n*S where S is concat string length (which will always be < 50)). On Thu, Dec 3, 2009 at 4:39 PM, Pavel Ivanov wrote: > Maybe > > select distinct bar.* from foo, bar > where foo.col2 = bar.col2 and foo.col3 = bar.col3 > > It's not clear from your requirements written below whether you need > 'distinct' here or not but I've added it just in case... > > > Pavel > > On Thu, Dec 3, 2009 at 4:34 PM, Rich Rattanni wrote: >> Suppose I have the following two tables >> >> foo: >> 10 | A | A >> 20 | B | B >> 30 | C | C >> >> and >> >> bar: >> 1 | A | X >> 2 | B | C >> 3 | A | A >> 4 | C | A >> 5 | B | B >> >> >> I want to select all the rows in table bar where the second and third >> column match an entry found in foo (that is to say, I want my result >> to be 3 | A | A and 5 | B | B. >> >> My attempt of: >> select * from bar >> where col2 in (select col2 from foo where bar.col2 = col2) >> and col3 in (select col3 from foo where bar.col3 = col3) >> >> Does not work, and I understand that is should not (it returns 2 | B | >> C, 3 | A | A, 4 | C | A, 5 | B | B). >> >> Could someone give me a hand? >> >> -- >> Rich >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Selecting unique entries from one table with multiple columns forming a unique key
Maybe select distinct bar.* from foo, bar where foo.col2 = bar.col2 and foo.col3 = bar.col3 It's not clear from your requirements written below whether you need 'distinct' here or not but I've added it just in case... Pavel On Thu, Dec 3, 2009 at 4:34 PM, Rich Rattanni wrote: > Suppose I have the following two tables > > foo: > 10 | A | A > 20 | B | B > 30 | C | C > > and > > bar: > 1 | A | X > 2 | B | C > 3 | A | A > 4 | C | A > 5 | B | B > > > I want to select all the rows in table bar where the second and third > column match an entry found in foo (that is to say, I want my result > to be 3 | A | A and 5 | B | B. > > My attempt of: > select * from bar > where col2 in (select col2 from foo where bar.col2 = col2) > and col3 in (select col3 from foo where bar.col3 = col3) > > Does not work, and I understand that is should not (it returns 2 | B | > C, 3 | A | A, 4 | C | A, 5 | B | B). > > Could someone give me a hand? > > -- > Rich > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Selecting unique entries from one table with multiple columns forming a unique key
Suppose I have the following two tables foo: 10 | A | A 20 | B | B 30 | C | C and bar: 1 | A | X 2 | B | C 3 | A | A 4 | C | A 5 | B | B I want to select all the rows in table bar where the second and third column match an entry found in foo (that is to say, I want my result to be 3 | A | A and 5 | B | B. My attempt of: select * from bar where col2 in (select col2 from foo where bar.col2 = col2) and col3 in (select col3 from foo where bar.col3 = col3) Does not work, and I understand that is should not (it returns 2 | B | C, 3 | A | A, 4 | C | A, 5 | B | B). Could someone give me a hand? -- Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
