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
Customer
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
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)