The MySQL online documentation doesn't include a MINUS command, just a
bunch of reference to minus signs (-).
Thankfully, there's more than one way to do this!
Also: having these handy new tools, I'm revisiting some old, cludgy
code, and I wonder if it's possible to use LIKE with the results of a
subquery, eg.:
SELECT * FROM table WHERE ip [NOT LIKE ANYTHING IN] (SELECT ip_range
FROM known_ips)
where [NOT LIKE ANYTHING IN] is probably some different wording.
I have script that combs through our logs to weed out spiders, bots and
whatnot, and it references a table full of known good IPs that are
definitely real users. Right now I have this hideous long query that
includes a "WHERE ip not like '136.227.%' and ip not like '123.345.%'
and...". If there's a way to similarly slim down this statement, I would
love to find it.
I have a feeling the SQL has much vaster powers than I know how to harness!
thanks,
Ken
Jonathan Gorman wrote:
> Last I checked MySQL doesn't support MINUS, but it's been a few years
> since I used it. I vaguly remember talk about the developers planning on
> adding it. I took a quick glance at the docs, but I can't seem to find
> anything one way or another. Is it in one of the later versions of
> MySQL?
>
>
> On Fri, 26 Jan 2007, Jeffrey Barnett wrote:
>
>> You have gotten a lot of suggestions, but here is one more.
>>
>> select * from lib_books where good_thing = 'TRUE'
>> MINUS
>> select * from lib_books where bad_thing = 'TRUE'
>>
>> I think MINUS is faster than JOIN.
>>
>> Other SET OPERATIONS include UNION and INTERSECT.
>>
>> Set operations require that the underlying result sets be "compatible":
>> Same number of columns.
>> Corresponding columns have matching datatypes.
>>
>>
>> Ken Irwin wrote:
>>> Hi all,
>>>
>>> Thanks for these myriad responses! I've gotten at least three distinct
>>> approaches to try. I knew there had to be a better way.
>>>
>>> your sql-fu is appreciated!
>>>
>>> joys
>>> Ken
>>
--
Ken Irwin
Reference Librarian
Thomas Library, Wittenberg University
|