 |
 |
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