List:General Discussion« Previous MessageNext Message »
From:shawn green Date:June 12 2013 5:57pm
Subject:Re: How do I select all rows of table that have some rows in another
table (AND, not OR)
View as plain text  
Hello Daevid,

On 6/11/2013 7:17 PM, Daevid Vincent wrote:
>
>
>> -----Original Message-----
>> From: shawn green [mailto:shawn.l.green@stripped]
>> Sent: Tuesday, June 11, 2013 2:16 PM
>> To: mysql@stripped
>> Subject: Re: How do I select all rows of table that have some rows in
>> another table (AND, not OR)
>>
>> Hello Daevid,
>>
>> ... snip ...
>
> Shawn, thank you for taking the time to reply. I wasn't expecting the
> solution to be so much "work" with multiple statements like that. I was
> thinking it could be done in one (or two, as in split out a portion of it in
> PHP and re-insert it to the original SQL to avoid a JOIN or something). Part
> of the issue is that we use PHP to generate the $sql string by appending
> bits and pieces depending on the search criteria thereby keeping the 'path'
> through the SQL statement simple and relatively linear. To implement this
> would require significant re-writing and/or special cases where we could
> introduce errors or omissions in the future. The frustrating part is that
> the REGEXP query we use now only takes about 2 seconds on my DEV VM (same
> database as PROD), however when the RDBMS is loaded it then takes up to 30
> seconds so in theory it's not even that inefficient given the # rows. We do
> use memcached for the results, but since there are so many combinations a
> user could choose, our hit ratio is not so great and therefore the cache
> isn't doing us much good and this is why the RDBMS can get loaded up easily.
>
> How can an "OR" be so simple using IN() but "AND" be so overly complex?
> Seems that mysql should have another function for ALL() that works just like
> IN() to handle this kind of scenario.
>
>

As I said, we could have used a single command but you would have not 
been able to review the 'best' match scenario only those rows that were 
'complete' matches.

And, those three commands can easily be encapsulated within a stored 
procedure. Pass in two strings (one listing the values to find, one 
listing the values to reject) and use PREPARED STATEMENTS within the 
procedure to build the IN() lists within the first and second commands. 
  The value in the 'HAVING' clause in the last command (unless you use 
the other option of reviewing the list of 'closest' matches) can be set 
to the number of items in the "list of things to find" parameter to your 
procedure.

I was demonstrating a principle you could use and not necessarily giving 
you a full solution.  Some tweaking may be required.

Also,  by encapsulating what I wrote within a stored procedure, this 
changes my 3 statements to a single call that you can easily configure 
from your PHP application.

Regards,
-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
How do I select all rows of table that have some rows in another table (AND, not OR)Daevid Vincent11 Jun
  • Re: How do I select all rows of table that have some rows in anothertable (AND, not OR)shawn green11 Jun
    • RE: How do I select all rows of table that have some rows in another table (AND, not OR)Daevid Vincent11 Jun
      • RE: How do I select all rows of table that have some rows inanother table (AND, not OR)Rick James12 Jun
        • RE: How do I select all rows of table that have some rows in another table (AND, not OR)Daevid Vincent12 Jun
          • RE: How do I select all rows of table that have some rows inanother table (AND, not OR)Rick James12 Jun
      • Re: How do I select all rows of table that have some rows in anothertable (AND, not OR)shawn green12 Jun
  • Re: How do I select all rows of table that have some rows inanother table (AND, not OR)hsv13 Jun
RE: How do I select all rows of table that have some rows in another table (AND, not OR)Daevid Vincent12 Jun
Re: How do I select all rows of table that have some rows inanother table (AND, not OR)hsv13 Jun
RE: How do I select all rows of table that have some rows in another table (AND, not OR)hsv18 Jun