Hi Jennifer,
great that it worked.
Try replacing the line
`ip` IN (temp_ip)
with
`ip` IN (SELECT ip FROM temp_ip)
Each subquery needs to be a complete SELECT query.
Thanks,
Roy
On 17.02.14 21:11, Jennifer wrote:
On Feb 17, 2014, at 10:17 AM, Roy Lyseng wrote:
please try filtering with a subquery that locates ip addresses with more than 1
attempt:
Hi Roy,
That did it! Thank you so much!!! Now I just need to study that IN
clause to see what's going on there.
If this is too slow, you may try materializing the subquery in a temporary
table and use that table instead of the subquery.
Yes, it is slow. It takes about 15 seconds to finish, but since it's
run once a day via cron to email me a report, I don't think it's too much of a
problem. However, I did try to see if I could do it, but there's an error
somewhere in the SQL. What am I doing wrong?
CREATE TEMPORARY TABLE temp_ip AS
(SELECT `ip`
FROM `ip_addresses`
WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND
CURDATE() - INTERVAL 1 SECOND)
AND TRIM(`referrer`) LIKE ''
GROUP BY `ip`
HAVING COUNT(*) > 1
);
SELECT `ip`, `page`, `url`, `time_stamp`
FROM `ip_addresses`
WHERE (`time_stamp` BETWEEN date_add( CURDATE(), INTERVAL -1 DAY ) AND
CURDATE() - INTERVAL 1 SECOND)
AND TRIM(`referrer`) LIKE '' AND
`ip` IN (temp_ip)
ORDER BY INET_ATON(`ip`), `time_stamp`;
Thank you again,
Jenni
Superior Shelving Systems::::....
The (Storage|Office|Home|Warehouse) Shelving Specialists
Since 1984
Workstation Chairs/Stools:
http://www.superiorshelving.com/mfg/nexel/pages/stools.php
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
