Re: Multiple AND on many-many-table.
Bob Hall wrote: I want to do a select like select id from test where id in (select id from test where value in (1,2,3) and id in (select id from test where value in (4,6,7) and id in (select id from test where value in (1,6,25) Even if MySQL supported subqueries, this query would always return the empty set. I'm really unsure about what you're trying to do, but I think the following comes close. SELECT id FROM test WHERE value IN (1, 2, 3, 4, 6, 7, 25); No the query I wrote would return the intersection of three lists of user ids where each list contained any users that had the values listed. Usually, the column name 'id' is used for a unique row identifier. Since you didn't give the table structure, I assumed that was the case here. If not, then your query could return rows. In that case, your temp table approach is probably the best solution. Bob Hall Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Multiple AND on many-many-table.
Does anybody know what is the fastest way to run a large multiple AND type query on a many-many table and the least memory hungry. Sir, what is a type query, and what in the world is a many-many table? For example if I have create table test (id int, value int); which has many-to-many on id, and value ? I want to do a select like select id from test where id in (select id from test where value in (1,2,3) and id in (select id from test where value in (4,6,7) and id in (select id from test where value in (1,6,25) Even if MySQL supported subqueries, this query would always return the empty set. I'm really unsure about what you're trying to do, but I think the following comes close. SELECT id FROM test WHERE value IN (1, 2, 3, 4, 6, 7, 25); So I must return users that qualify for at least one value in several lists, obviously this is somewhat easy with subselects... Without subselects I'm doing this, can anybody tell me of a better way? create table temp (id int not null, key (id)) type=heap; insert into temp select id from test where value in (1,2,3); insert into temp select id form test where value in (4,6,7); insert inot temp select id from test where value in (1,6,25); select id from temp, count(*) as numlines from temp group by id having numlines = 3; drop table temp; there will be anywhere between 1 and 10 of the above lists for this query so then I return the qulified useres by doing this on the heap table where the having part is equal to the number of lists that were checked. Obviously the temp table can get very large depending the query, currently this seems pretty fast, but I'm concerned that it will blow up on me as I get more id values anybody know a better way? A self join comes to mind but if there are many users and say 10 questions then the length of the self join result before the WHERE is userlines^10 which can be a very big number. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Know thyself? Absurd direction! Bubbles bear no introspection. -Khushhal Khan Khatak - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Multiple AND on many-many-table.
Bob Hall wrote: I want to do a select like select id from test where id in (select id from test where value in (1,2,3) and id in (select id from test where value in (4,6,7) and id in (select id from test where value in (1,6,25) Even if MySQL supported subqueries, this query would always return the empty set. I'm really unsure about what you're trying to do, but I think the following comes close. SELECT id FROM test WHERE value IN (1, 2, 3, 4, 6, 7, 25); No the query I wrote would return the intersection of three lists of user ids where each list contained any users that had the values listed. Your query would return all user ID's who had any of the number listed which is not what I need... but thanks... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Multiple AND on many-many-table.
Steve Ruby wrote: Does anybody know what is the fastest way to run a large multiple AND type query on a many-many table and the least memory hungry. For example if I have create table test (id int, value int); which has many-to-many on id, and value I want to do a select like select id from test where id in (select id from test where value in (1,2,3) and id in (select id from test where value in (4,6,7) and id in (select id from test where value in (1,6,25) So I must return users that qualify for at least one value in several lists, obviously this is somewhat easy with subselects... Without subselects I'm doing this, can anybody tell me of a better way? create table temp (id int not null, key (id)) type=heap; insert into temp select id from test where value in (1,2,3); insert into temp select id form test where value in (4,6,7); insert inot temp select id from test where value in (1,6,25); select id from temp, count(*) as numlines from temp group by id having numlines = 3; drop table temp; there will be anywhere between 1 and 10 of the above lists for this query so then I return the qulified useres by doing this on the heap table where the having part is equal to the number of lists that were checked. Obviously the temp table can get very large depending the query, currently this seems pretty fast, but I'm concerned that it will blow up on me as I get more id values anybody know a better way? A self join comes to mind but if there are many users and say 10 questions then the length of the self join result before the WHERE is userlines^10 which can be a very big number. Not knowing how HAVING is optimized i've decided for now to go with one table per question (each of the ANDs) and do a join (max of 10way) against a temp table for each question... still doesn't sound like the best route but it will have the same amount of data in it as if I did one big table and seems like it would be quicker to update the keys on the smaller tables better ideas appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
