How about "select Incidents.* from Incidents inner join Calls on where Calls.status='Open'"?

Thank you,
Micah Gersten
onShore Networks
Internal Developer

Dee Ayy wrote:
> A database was designed with the following tables:
> Incidents
> _________
> id (auto incremented int)
> ...
> Calls
> _____
> id (auto incremented int)
> incidentId (foreign key)
> status (varchar 32)
> ...
> The status of the last Call is the status of the related Incident.
> Statuses can be "Not started" through various states up to "Completed".
> The status column never has the text "Open".
> If the last Call for the related Incident is not "Completed", then it
> is considered to be "Open".
> My task is to getIncidentsWithStatus("Open") using PHP.
> The existing inefficient method is in the PHP function
> getIncidentsWithStatus($status = "Open"), made worse by mingling with
> PHP and then another MySQL query.  It first finds
> $theHugeListOfIncidentIds of the last Calls OF ALL INCIDENTS, then
> uses IN ($theHugeListOfIncidentIds) AND Calls.status NOT LIKE
> 'Completed'.  The reason this was done was that if Calls.status NOT
> LIKE 'Completed' was used first, then the result would include all
> Incidents.
> A) What would be an efficient MySQL query with the database in the
> present state to getIncidentsWithStatus("Open")?
> I can think of two alternatives, which require the database to be modified:
> 1a) Add a trigger to update a new column named "statusFromCall" in the
> Incidents table when the Calls.status is updated.
> 1b) Add PHP code to update the new column named "statusFromCall" in
> the Incidents table when the Calls.status is updated.
> 2) Then just query for Incidents WHERE statusFromCall NOT LIKE 'Completed'.
> B) What would be the MySQL query to create such a trigger in 1a?
> Thanks.

PHP Database Mailing List (
To unsubscribe, visit:

Reply via email to