Finding Data in One of Two Tables
I'm using this query in a Perl program: SELECT Distinct x.Search FROM $source.Searches AS x LEFT JOIN searching.Status AS s ON x.Search=s.Search AND s.Source='$source' WHERE x.RedoTime'$now' AND s.Search IS NULL This program runs other programs that do internet searches. I have different sources (stored in $source, of course), and source has it's own DB with a table, Searches. Each row of searches describes a different search that can be done and each search has a name, which is stored in the Searches column (within the Searches table, so, yes, I use that name for a table and a column). When a search is being executed, an entry is placed in searching.Status, with one row in that table showing the status of the search. So if I have a source named alpha and searches named one and two and the system is executing the search one, not only is there a row in alpha.Searches describing one in depth, but there is a row in searching.Status describing the progress with one. When each search is done, the RedoTime is set so it's easy to see when it needs to be executed again. What I want to do is get a list of searches in the Searches table (within the source's DB) that are NOT listed in Status and where the RedoTime is before $now (the current time). From what I've read, the query above should do it, but I have this nagging feeling I've done something wrong. Will that query pick up all rows in $source.Searches that have a RedoTime before $now that are NOT also listed in searching.Status? Thanks for any help on this! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
map polygon data for popular us cities
dear list, where can i find a list of map polygons for united states cities? any open database? or tool to obtain correct coordinates? i googled but couldn't find anything useful.. may be the terms i use are not the correct keywords :( any help or advice would be really appreciated. ~viraj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with query.
Hi Paul! Paul Halliday wrote: I have a query (thanks to this list) that uses a join to add country information to an IP. It looks like this: SELECT COUNT(signature) AS count, INET_NTOA(src_ip), map1.cc as src_cc, INET_NTOA(dst_ip), map2.cc as dst_cc FROM event LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip LEFT JOIN mappings AS map2 ON event.dst_ip = map2.ip WHERE timestamp BETWEEN '2011-01-29 00:00:00' AND '2011-01-30 00:00:00' GROUP BY src_ip, src_cc, dst_ip, dst_cc ORDER BY src_cc, dst_cc ASC; This would return something like this: +---+---++---++ | count | INET_NTOA(src_ip) | src_cc | INET_NTOA(dst_ip) | dst_cc | +---+---++---++ | 8 | 10.0.0.8 | NULL | 61.55.142.129 | CN | | 1 | 210.52.216.92 | CN | 10.0.0.2 | NULL | | 1 | 121.33.205.235| CN | 172.16.0.6| NULL | | 239 | 210.52.216.92 | CN | 10.0.0.2| NULL | | 2 | 121.33.205.235| CN | 172.16.0.15 | NULL | | 4 | 121.33.205.235| CN | 10.0.0.1| NULL | |39 | 210.52.216.92 | CN | 172.16.0.15 | NULL | | 1 | 121.33.205.235| CN | 172.16.0.14 | NULL | +---+---++---++ All I am interested in is the event count for each country, in this case: 295 CN ... Other countries.. As a first step, remove the columns src_ip and dst_ip from your query, both from the select list and from the group by (but not from the join condition): SELECT COUNT(signature) AS count, map1.cc as src_cc, map2.cc as dst_cc FROM event LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip LEFT JOIN mappings AS map2 ON event.dst_ip = map2.ip WHERE timestamp BETWEEN '2011-01-29 00:00:00' AND '2011-01-30 00:00:00' GROUP BY src_cc, dst_cc ORDER BY src_cc, dst_cc ASC; The result should be: +---+++ | count | src_cc | dst_cc | +---+++ | 8 | NULL | CN | | 287 | CN | NULL | +---+++ Now, you are left with two problems: 1) Your query still groups by the country codes of both source and destination (which doesn't become obvious with your sample data, as one of these is always shown as NULL). For example: traffic just between three countries (each combination occurring) would give nine rows, with each country occurring three times as source and three times as destination. If you want the total for the source country, you must stop grouping by destination (and vice versa). 2) If you really want the total of source and destination (the 295 in your example, not the 287 and 8 I expect from my version), it really might be easiest to do this in the application; I have no idea how to do it nicely in SQL. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with query.
On Tue, 1 Feb 2011 14:46:39 -0400 Paul Halliday paul.halli...@gmail.com wrote: I have a query (thanks to this list) that uses a join to add country information to an IP. It looks like this: SELECT COUNT(signature) AS count, INET_NTOA(src_ip), map1.cc as src_cc, INET_NTOA(dst_ip), map2.cc as dst_cc FROM event LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip LEFT JOIN mappings AS map2 ON event.dst_ip = map2.ip WHERE timestamp BETWEEN '2011-01-29 00:00:00' AND '2011-01-30 00:00:00' GROUP BY src_ip, src_cc, dst_ip, dst_cc ORDER BY src_cc, dst_cc ASC; . All I am interested in is the event count for each country, in this case: 295 CN ... Other countries.. try: SELECT COUNT(*) AS count, IF(map1.cc, map1.cc, map2.cc) AS country FROM event LEFT JOIN mappings AS map1 ON event.src_ip = map1.ip LEFT JOIN mappings AS map2 ON event.dst_ip = map2.ip WHERE timestamp BETWEEN '2011-01-29 00:00:00' AND '2011-01-30 00:00:00' GROUP BY country ORDER BY country note, I am assuming in this query that you have either a source country or a destination country, but not both. If both values might be set, and you need to count each, this will not work. Thanks! -- Paul Halliday http://www.pintumbler.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=sim...@syounger.com -- Simcha Younger sim...@syounger.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: map polygon data for popular us cities
On Wed, Feb 2, 2011 at 11:30 AM, viraj kali...@gmail.com wrote: dear list, where can i find a list of map polygons for united states cities? any open database? or tool to obtain correct coordinates? A bit offtopic here, but I suspect that most such databases will be proprietary and thus payable through the nose. Have a look at the OpenStreetMap project, I suspect their database might be accessible under some open license. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
RE: map polygon data for popular us cities
Openstreetmap.org is as close as you'll get. I'd be surprised if they have shapes for cities beyond just lat/lon point data, but they should have shapes data for zips, counties, states and countries if I recall correctly. -Original Message- From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Wednesday, February 02, 2011 7:59 AM To: viraj Cc: mysql@lists.mysql.com Subject: Re: map polygon data for popular us cities On Wed, Feb 2, 2011 at 11:30 AM, viraj kali...@gmail.com wrote: dear list, where can i find a list of map polygons for united states cities? any open database? or tool to obtain correct coordinates? A bit offtopic here, but I suspect that most such databases will be proprietary and thus payable through the nose. Have a look at the OpenStreetMap project, I suspect their database might be accessible under some open license. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel IMPORTANT: This email message is intended only for the use of the individual to whom, or entity to which, it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are NOT the intended recipient, you are hereby notified that any use, dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please reply to the sender immediately and permanently delete this email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: map polygon data for popular us cities
thank you all for the links and notes. ~viraj On Thu, Feb 3, 2011 at 7:08 AM, Gavin Towey gto...@ffn.com wrote: Openstreetmap.org is as close as you'll get. I'd be surprised if they have shapes for cities beyond just lat/lon point data, but they should have shapes data for zips, counties, states and countries if I recall correctly. -Original Message- From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Wednesday, February 02, 2011 7:59 AM To: viraj Cc: mysql@lists.mysql.com Subject: Re: map polygon data for popular us cities On Wed, Feb 2, 2011 at 11:30 AM, viraj kali...@gmail.com wrote: dear list, where can i find a list of map polygons for united states cities? any open database? or tool to obtain correct coordinates? A bit offtopic here, but I suspect that most such databases will be proprietary and thus payable through the nose. Have a look at the OpenStreetMap project, I suspect their database might be accessible under some open license. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel IMPORTANT: This email message is intended only for the use of the individual to whom, or entity to which, it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are NOT the intended recipient, you are hereby notified that any use, dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please reply to the sender immediately and permanently delete this email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
