Neil Sverdloff Neil Sverdloff - 4 months ago 4
SQL Question

Mysql query code improvement

I am fairly new to coding and through my own interest I am attempting to create a database system for work and very much learning as I go along.

I have a database query which is designed to show recent results where

Sample Name
,
Tank Name
and
Customer
match. These are from three different but related tables.

I have come up with the following query. This Query Works but for my own education am I working along the right lines or is there a better way of approaching this problem (this looks very untidy and hard to read to my mind)?

SELECT `t_workorders`.`Work Order`, `t_workorders`.`Date Sampled`, `Result`,`t_test_units`.`Unit` FROM `t_sampletests` RIGHT JOIN `t_samples` on `Sample` = `pk_Sample`
right join `t_workorders` on `t_samples`.`Work Order` = `t_workorders`.`Work Order` LEFT JOIN `t_test_units`ON `t_sampletests`.`Unit` = `t_test_units`.`pk_Unit`
WHERE `t_sampletests`.`Test` = (select `Test` FROM `t_sampletests` WHERE `pk_SampleTest` = $testID)
AND `t_Samples`.`Sample Name` = (select `Sample Name` from `t_sampletests` LEFT JOIN `t_Samples` ON `Sample` = `pk_Sample`
WHERE `pk_SampleTest` = $testID) AND `t_Samples`.`Tank` = (select `Tank` from `t_sampletests` LEFT JOIN `t_Samples` ON `Sample` = `pk_Sample`
WHERE `pk_SampleTest` = $testID) AND `t_workorders`.`Customer`= (select `Customer` from `t_sampletests` LEFT JOIN `t_Samples` ON `Sample` = `pk_Sample`
LEFT JOIN `t_workorders` ON `t_Samples`.`Work Order` = `t_workorders`.`Work Order` WHERE `pk_SampleTest` = $testID) AND `t_workorders`.`Work Order` <>
(select `t_workorders`.`Work Order` from `t_sampletests` LEFT JOIN `t_Samples` ON `Sample` = `pk_Sample` LEFT JOIN `t_workorders` ON `t_Samples`.`Work Order` = `t_workorders`.`Work Order`
WHERE `pk_SampleTest` = $testID) ORDER BY `t_workorders`.`Work Order` DESC LIMIT 15


Any suggestions of alternative or more suitable approaches would be greatly appreciated.

Many Thanks

Answer

IMO, using new lines and increments will help you read and structure the query and allow you to have a quick overview of what it does.

I also try to be consistent when using SQL keywords and write them in upper case.

SELECT `t_workorders`.`Work Order`, `t_workorders`.`Date Sampled`, `Result`,`t_test_units`.`Unit`
FROM `t_sampletests`
RIGHT JOIN `t_samples` ON `Sample` = `pk_Sample` 
RIGHT JOIN `t_workorders` ON `t_samples`.`Work Order` = `t_workorders`.`Work Order`
LEFT JOIN `t_test_units`ON `t_sampletests`.`Unit` = `t_test_units`.`pk_Unit`
WHERE `t_sampletests`.`Test` = (
    SELECT `Test` FROM `t_sampletests` WHERE `pk_SampleTest` = $testID
  )
  AND `t_Samples`.`Sample Name` = (
    SELECT `Sample Name`
    FROM `t_sampletests`
    LEFT JOIN `t_Samples` ON `Sample` = `pk_Sample` WHERE `pk_SampleTest` = $testID
  )
  AND `t_Samples`.`Tank` = (
    SELECT `Tank`
    FROM `t_sampletests`
    LEFT JOIN `t_Samples` ON `Sample` = `pk_Sample` WHERE `pk_SampleTest` = $testID
  )
  AND `t_workorders`.`Customer`= (
    SELECT `Customer`
    FROM `t_sampletests`
    LEFT JOIN `t_Samples` ON `Sample` = `pk_Sample`
    LEFT JOIN `t_workorders` ON `t_Samples`.`Work Order` = `t_workorders`.`Work Order`
    WHERE `pk_SampleTest` = $testID
  )
  AND `t_workorders`.`Work Order` <> (
    SELECT `t_workorders`.`Work Order`
    FROM `t_sampletests`
    LEFT JOIN `t_Samples` ON `Sample` = `pk_Sample`
    LEFT JOIN `t_workorders` ON `t_Samples`.`Work Order` = `t_workorders`.`Work Order` 
    WHERE `pk_SampleTest` = $testID
  )
ORDER BY `t_workorders`.`Work Order`
DESC LIMIT 15;

(and don't forget the ; at then end of your query)