CODEWITHSUNDEEP
mysqlsql
Josh Curren
Josh Curren

Reputation: 10226

How can I improve this SQL to avoid several problems with its results?

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"

Upvotes: 0

Views: 80

Answers (1)

Sparky
Sparky

Reputation: 15105

Your code does a JOIN between Maintenance and Maintenace_parts The first thing you will need to do is convert this to a LEFT JOIN

  FROM maintenance M 
  JOIN truck T ON t.truck_id=M.some_id 
  LEFT JOIN maintenance_parts P ON M.maintenance_id = P.maintenance_id
  WHERE...

After you do this, you will get null values for every column in maintenance_parts if a matching row is not found. It appears your code will be OK with this, but make sure your select and where clause MATCH function properly work with NULLs

Code to check/review that is works properly with NULLS is the following

 OR MATCH( P.part_num, P.part_desc, P.part_ref) AGAINST( '$keywords' )

The second problems appears to be that you are grouping by maintenance_id instead of truck_id. Try changing the GROUP BY field, it should solve your second issue

Revised query below

    SELECT T.TRUCK_ID,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' ) +
           IfNull(P.PartScore,0) +
           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, 
                    JOIN 

                    (SELECT Truck_id,truck_number, make, model, engine, vin_number,            transmission_number,comments FROM Truck
UNION
SELECT Truck_id,truck_number, make, model, engine, vin_number,            transmission_number,comments FROM Trailer
)  

T 
                     ON M.some_id=T.Truck_id
                    LEFT JOIN 
                    (SELECT maintenance_id,SUM(MATCH( P.part_num, P.part_desc, P.part_ref)               AGAINST( '$keywords' )) AS PartScore
                     FROM  maintenance_parts
                     GROUP BY maintenance_id ) P
                    P ON M.maintenance_id = P.maintenance_id
                    WHERE 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' 
    ORDER BY score DESC, maintenance_id DESC 
    LIMIT 0, $limit"

Upvotes: 1

Related Questions