Firstly I would like to apologize for the Title name, I just had no idea how to name it correctly.
My problem is: I have two tables, a table with data and the second a log table. I can't change the tables specs because the software we bought is using this tables and changes will brake something. This data is not from real world, the real data is something else but the structure and the sample is correctly chosen.
Data table has a structure like this:
id | name | status | path |
a1 | worker1 | alfa | loc1.txt |
a2 | worker2 | beta | loc2.txt |
a3 | worker3 | alfa | loc3.txt |
b1 | security1 | test1 | loc8.txt |
a4 | worker3 | delta | loc4.txt |
c4 | wireless7 | comm | loc9.txt |
a5 | worker1 | sigmna | loc5.txt |
a6 | worker1 | gama | loc6.txt |
LOG table has a structure like this:
lid | ldate | ltime |
a1 | 2011-10-10 | 10:19:25 |
a2 | 2011-10-11 | 08:23:18 |
a6 | 2011-10-10 | 14:19:25 |
a3 | 2011-10-10 | 10:19:25 |
a1 | 2011-10-11 | 10:22:13 |
a1 | 2011-10-13 | 18:19:25 |
a4 | 2011-10-11 | 10:19:25 |
a5 | 2011-10-11 | 10:22:48 |
a6 | 2011-10-12 | 10:19:25 |
What I have to do is randomly select ID from data table with this conditions:
- select all where id should start with "a%"
- a selected worker must not repeat with the same status in next 24 hours.
- a selected worker could be repeated with different status, but not before 4 hours
- limit by 80 results, in this results the same worker must not repeated as we are building a list
What I currently have is:
SELECT A.id, A.name, A.status, A.path
FROM data A LEFT OUTER JOIN log B ON A.id=B.lid AND
((str_to_date(CONCAT(B.ldate,' ',B.ltime),'%Y-%m-%d %H:%i:%s') < NOW() - INTERVAL 24 HOUR))
WHERE A.id like "a%" order by A.name limit 1,80
This query currently removes everything that should't be repeated in 24 hours, but if I remove that I can't correctly add the condition, that a worker with different status can be selected but not before 4 hours. I am stuck for a couple of days now....help appreciated.