Re: Help to improve MySQL query

How about "select Incidents.* from Incidents inner join Calls on Incidents.id=Calls.incidentid where Calls.status='Open'"? Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com 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 Calls.id 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. > >