The following query takes forever to complete and I'm not exactly an SQL guru so I was hoping some of the experts here could take a quick look at it. Can you see anything seriously wrong with it?
LEFT JOIN ord_reports r1 ON (ord_workorders.id = r1.workorderid AND r1.invoiced = 1))
LEFT JOIN ord_reports r2 ON (ord_workorders.id = r2.workorderid AND r2.invoiced <> 1)
WHERE ord_workorders.completed = 1
AND (r1.workorderid IS NULL OR r2.workorderid IS NOT NULL)
GROUP BY ord_workorders.id ORDER BY ord_workorders.title DESC
When I run it in phpMyAdmin I get one column full of question marks that says (when I hover over them) "You should define a primary key for this table". I've got primary keys on all tables, so what does this message mean?
I'll try my best to explain. There are work orders (ord_workorders) and, in another table (ord_reports) there are "reports" that belong to a work order. The results of this query is supposed to show completed work orders.
A work order is considered completed when the "completed" column is "1", but if the work order contains one or more reports, and all of these reports are "invoiced", it should not be included in the result, because those work orders are called "invoiced work orders" and they are to be displayed on another page.
To sum up, I'm looking for work orders (ord_workorders) that:
1. have been set as "completed"
2. have got at least one report (ord_reports) that is not set as "invoiced"
Thanks very much in advance for anything that could help me!