How can I improve this SQL to avoid several problems with its results?
- by Josh Curren
I am having some problems with trying to search. Currently this will only return results that have at least 1 row in the maintenance_parts table. I would like it to return results even if there are 0 parts rows.
My second problem is that when you search for a vehicle and it should return multiple results (multiple maintenance rows) it will only return 1 result for that vehicle.
Some Background Info:
The user has 2 fields to fill out. The fields are vehicle and keywords. The vehicle field is meant to allow searching based on the make, model, VIN, truck number (often is 2 - 3 digits or a letter prefix followed by 2 digits), and a few other fields that belong to the truck table. The keywords are meant to search most fields in the maintenance and maintenance_parts tables (things like the description of the work, parts name, parts number).
The maintenance_parts table can contain 0, 1, or more rows for each maintenance row. The truck table contains exactly 1 row for every maintenance row. A truck can have multiple maintenance records.
"SELECT M.maintenance_id, M.some_id, M.type_code, M.service_date, M.mileage, M.mg_id, M.mg_type, M.comments, M.work_done,
MATCH( M.comments, M.work_done) AGAINST( '$keywords' ) +
MATCH( P.part_num, P.part_desc, P.part_ref) AGAINST( '$keywords' ) +
MATCH( T.truck_number, T.make, T.model, T.engine, T.vin_number, T.transmission_number, T.comments) AGAINST( '$vehicle' )
AS score
FROM maintenance M, maintenance_parts P, truck T
WHERE M.maintenance_id = P.maintenance_id
AND M.some_id = T.truck_id
AND M.type_code = 'truck'
AND (
(MATCH( T.truck_number, T.make, T.model, T.engine, T.vin_number, T.transmission_number, T.comments) AGAINST( '$vehicle' )
OR T.truck_number LIKE '%$vehicle%')
OR MATCH( P.part_num, P.part_desc, P.part_ref) AGAINST( '$keywords' )
OR MATCH( M.comments, M.work_done) AGAINST( '$keywords' )
)
AND M.status = 'A' GROUP BY maintenance_id ORDER BY score DESC, maintenance_id DESC LIMIT 0, $limit"
