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"