Mysql query code improvement

Question

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

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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)

Mysql query code improvement

Sign up