Neil Sverdloff - 6 months ago 10

SQL 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`

`Customer`

I have come up with the following query.

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