Re: Tricky query
Can this be expressed somewhat more elegantly than multiple EXISTS subqueries? SELECT DISTINCT key FROM keyval outer WHERE EXISTS (SELECT * FROM keyval inner WHERE outer.key = inner.key AND inner.val = 8) AND EXISTS (SELECT * FROM keyval inner WHERE outer.key = inner.key AND inner.val = 9) Would you be looking for ... SELECT DISTINCT key FROM keyval k1 JOIN keyval k2 USING (key) WHERE k1.val IN(8,9,10); ? PB - Morten wrote: Hi, Given the table keyval(key int(11), val int(11)), I would like to be able to retrieve the keys for which a row exist for given X values. Example: key value 18 19 110 28 38 310 48 49 410 411 Given values 8, 9, 10 the query should thus return 1 and 4. The possible number of values is variable. Can this be expressed somewhat more elegantly than multiple EXISTS subqueries? SELECT DISTINCT key FROM keyval outer WHERE EXISTS (SELECT * FROM keyval inner WHERE outer.key = inner.key AND inner.val = 8) AND EXISTS (SELECT * FROM keyval inner WHERE outer.key = inner.key AND inner.val = 9) ... Br, Morten -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.5/406 - Release Date: 8/2/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky query
Peter Brawley wrote: Can this be expressed somewhat more elegantly than multiple EXISTS subqueries? SELECT DISTINCT key FROM keyval outer WHERE EXISTS (SELECT * FROM keyval inner WHERE outer.key = inner.key AND inner.val = 8) AND EXISTS (SELECT * FROM keyval inner WHERE outer.key = inner.key AND inner.val = 9) Would you be looking for ... SELECT DISTINCT key FROM keyval k1 JOIN keyval k2 USING (key) WHERE k1.val IN(8,9,10); The tricky part is that there must be a record for EACH of the values (8, 9, 10) and not just any one value (as IN requires). Your proposal will return 1,2,3,4 and not just 1,4. Br, Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky query
Morten wrote: Hi, Given the table keyval(key int(11), val int(11)), I would like to be able to retrieve the keys for which a row exist for given X values. Example: key value 18 19 110 28 38 310 48 49 410 411 Given values 8, 9, 10 the query should thus return 1 and 4. The possible number of values is variable. Can this be expressed somewhat more elegantly than multiple EXISTS subqueries? SELECT DISTINCT key FROM keyval outer WHERE EXISTS (SELECT * FROM keyval inner WHERE outer.key = inner.key AND inner.val = 8) AND EXISTS (SELECT * FROM keyval inner WHERE outer.key = inner.key AND inner.val = 9) ... Br, Morten How about: SELECT DISTINCT `key`, COUNT(`key`) AS c FROM `table` WHERE `value` in (8,9,10) HAVING c=3; -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky query
Morten wrote: Hi, Given the table keyval(key int(11), val int(11)), I would like to be able to retrieve the keys for which a row exist for given X values. Example: key value 18 19 110 28 38 310 48 49 410 411 Given values 8, 9, 10 the query should thus return 1 and 4. The possible number of values is variable. Can this be expressed somewhat more elegantly than multiple EXISTS subqueries? SELECT DISTINCT key FROM keyval outer WHERE EXISTS (SELECT * FROM keyval inner WHERE outer.key = inner.key AND inner.val = 8) AND EXISTS (SELECT * FROM keyval inner WHERE outer.key = inner.key AND inner.val = 9) ... Br, Morten I forgot the GROUP BY How about: SELECT DISTINCT `key`, COUNT(`key`) AS c FROM `table` WHERE `value` in (8,9,10) GROUP BY `key` HAVING c=3; -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky query
How about: SELECT DISTINCT `key`, COUNT(`key`) AS c FROM `table` WHERE `value` in (8,9,10) HAVING c=3; Clever! Thanks :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: tricky query
This is a bit vague. How about showing what you have done, and how it is not what you wan't. Colleen Dick wrote: I am building a SELECT using a left join and I almost have what I want. One of the fields that gets sucked in from the left join is either null or has a value in it. if it is null I only want to include the rows from the main table where the field isdemo=1 if that field has a value in it I want all the rows that match otherwise. Is there any way to do it all in one query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tricky Query...
[EMAIL PROTECTED] wrote: Here is a FABRICATED table I have TYPED OUT to demonstrate the information that I'm looking for. I'm looking for a single query that can produce: +--+--++---+ | clientid | revision | name | address | +--+--++---+ |1 |1 | Fred Flintstone| 145 Stonewall Terrace | |2 |0 | Barney Rubble | 57 Bedrock Way| |3 |2 | Pebbles Flintstone | 25 Mammoth Road | |4 |1 | Bam Bam Rubble | 25 Mammoth Road | +--+--++---+ if it were legal and it worked, I could do a SELECT clientid, revision, name, address FROM foobar WHERE revision = max(revision) GROUP BY clientid; I dunno - I'm thinking I have to do something with the indexes that I'm missing. Any help would be appreciated. Just thinking through this... One possibility would be to run multiple selects. You could do the SELECT max(revision), clientid FROM foobar GROUP BY clientid first, then programmatically either cycle through all of them doing mini-selects or glop together a giant or statement (probably that will fail when you have large numbers of clients). You could create a temporary table, use an INSERT ... SELECT statement to populate it with the above data, then join that table with your original table. You could programmatically maintain a 'most recent' flag which would be update whenever a new record was inserted. I'm going to set up a fake DB like yours and experiment with it. ... ... Here we go, this seems to work: SELECT t1.clientid, max(t1.revision) as maxrev, t2.revision, t2.name, t2.address FROM foobar t1, foobar t2 WHERE t1.clientid=t2.clientid GROUP BY clientid, revision HAVING maxrev=revision; See if that helps any. -- John Klein, Database Applications Developer | Omnia Mutantur, Systems Group - Harvard Law School | Nihil Interit - 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
