Databases
StackOverflow2013
Postsdbo
POMySQL query construction
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL query construction
    primarykey
    data
    text
    <p>I have a query problem. I have a table of agents that do things. I keep track of the things they do in an events table. I want to keep my agents busy but not too busy, so I need a query that will return me a group of agents that have done no more that 10 events in the past 10 minutes and no more than 400 events in the past 24 hours. And from this pool of available agents I can choose one to give something to do</p> <p>So my agent table looks something like:</p> <pre><code>Agent table AgentID. AgentName 1 Bob 2 Sue Event Table Event ID. Agent ID. Event Timestamp 1 2 1319525462 2 1 1319525462 3 2 1319525462 </code></pre> <p>Obviously these tables are just to give the form of the db. What I need generally and have not been able to figure out is how to select a group of agents from a join that returns a group of agents that have done no more than 10 events in the past 10 min and no more than 400 events in the past 24 hours. My actual tables are more complex, but I am just looking for a general principle on how to structure a query that would return the desired result. Thanks ahead of time for the help!</p> <p>**UPDATE building on Benoit's answere I came up with this:</p> <pre><code>SELECT DISTINCT username FROM campaign_agents LEFT OUTER JOIN (SELECT count(event_index) myevents, field_event_agent_value FROM new_event WHERE field_event_time_value BETWEEN 1320206138 AND 1320292538 GROUP BY field_event_agent_value ) last_24_hours ON last_24_hours.field_event_agent_value = campaign_agents.username LEFT OUTER JOIN (SELECT count(event_index) myevents, field_event_agent_value FROM new_event WHERE field_event_time_value BETWEEN 1320291938 AND 1320292538 GROUP BY field_event_agent_value ) last_10_mins ON last_10_mins.field_event_agent_value = campaign_agents.username WHERE last_24_hours.myevents &lt; 550 AND last_10_mins.myevents &lt; 10 </code></pre> <p>But it doesn't get the agents in the campaign_agents table who haven't done anything yet and are therefore not in the events table. Shouldn't a LEFT OUTER JOIN include everything in the first table, campaign_agents, even if there are no matches to the second table? Do I need to put and OR statement after the where to somehow get them included?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USvaene
    1. USvaene
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL query construction
      1. USo0'.
      1. VTFavorite
    2. VO
      singulars
      1. POMySQL query construction
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMySQL query construction
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload