MariusGM
asked on
SQL Statement Help
Hi All,
I require a statement that will indidcate whether a record is 'high' given a variable stored in another table.
sample ( sample_ID {pk}, job_ID{fk} )
result ( result_ID{pk}, sample_ID{fk}, job_ID{fk}, test_ID{fk}, result_value)
test ( test_ID{pk}, test_name, test_high )
'test_high' stores the high variable for any given 'test_ID' which would need to be checked against the 'result_value' in result.
DB is MySQL. Statement intended to be used in online PHP application.
Hope this makes sense, on standby to clarify. Any help much appreciated. M.
I require a statement that will indidcate whether a record is 'high' given a variable stored in another table.
sample ( sample_ID {pk}, job_ID{fk} )
result ( result_ID{pk}, sample_ID{fk}, job_ID{fk}, test_ID{fk}, result_value)
test ( test_ID{pk}, test_name, test_high )
'test_high' stores the high variable for any given 'test_ID' which would need to be checked against the 'result_value' in result.
DB is MySQL. Statement intended to be used in online PHP application.
Hope this makes sense, on standby to clarify. Any help much appreciated. M.
I assume you are not implementing a business object solution, since this kind of logic seems perfect for business logic, not SQL contrivances.
However, for a pure SQL solution you might have to clarify this a little better (or maybe it is just me! ;-)
You have three tables, one holds the result of a test (Result) another holds a test and its current highest schore, and I can't tell what the purpose of Sample is for.
Also, is the test_high attribute stored once for a test, or is it recalculated and stored every time a new result is stored?
However, for a pure SQL solution you might have to clarify this a little better (or maybe it is just me! ;-)
You have three tables, one holds the result of a test (Result) another holds a test and its current highest schore, and I can't tell what the purpose of Sample is for.
Also, is the test_high attribute stored once for a test, or is it recalculated and stored every time a new result is stored?
ASKER
Sorry guys, allow me to clarify.
The 'test' table stores the types of tests and thier high limit (test_high) available for 'samples'. The result table is a link table which stores the sample_ID and test_ID and contains a result_value column.
So the 'result' table might look as follows:
result_ID sample_ID test_ID result_value
1 1 1 1200
2 1 2 302
3 1 3 304
4 2 1 <100
5 2 3 1000
With the 'test' table like so:
test_ID test_name test_high
1 TVC 37 >3200
2 TVC 22 >3200
3 E.Coli >201
...
The desired result using the above example would be:
sample_ID test_name
1 E.Coli
5 E.Coli
Hope this helps, I might need to employ a bit of PHP to carry out the logic. Tried to submit to the PHP & Databases cat on EE but couldn't find.
The 'test' table stores the types of tests and thier high limit (test_high) available for 'samples'. The result table is a link table which stores the sample_ID and test_ID and contains a result_value column.
So the 'result' table might look as follows:
result_ID sample_ID test_ID result_value
1 1 1 1200
2 1 2 302
3 1 3 304
4 2 1 <100
5 2 3 1000
With the 'test' table like so:
test_ID test_name test_high
1 TVC 37 >3200
2 TVC 22 >3200
3 E.Coli >201
...
The desired result using the above example would be:
sample_ID test_name
1 E.Coli
5 E.Coli
Hope this helps, I might need to employ a bit of PHP to carry out the logic. Tried to submit to the PHP & Databases cat on EE but couldn't find.
This would be MUCH easier if you had only the numbers in those fields, not mathematical symbols. Is changing that an option?
ASKER
No sadly it is not. As result counts are capped at certain levels and require a more than or less than prefix.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Joe, My intention is to implement a joint PHP, MySQL Solution. However due to the new question wizard I was unable to submit it to my desired category of 'PHP & Databases' I am aware of the complexity that a pure SQL solution involves therefore anyone who could provide a combined PHP solution or advise on how to move category is most welcome.
Daniel, I shall give your solution a go, am out of office now. However I would like to implement something that I can hand over effectivly as and when. Thanks all.
Daniel, I shall give your solution a go, am out of office now. However I would like to implement something that I can hand over effectivly as and when. Thanks all.
You are trying to solve a complex problem. The solution will be complex.
Somewhere, you will select the records from Test. Somewhere you will iterate those records and build SQL statements and execute them.
Whether you do it in PHP or in SQL, the basic complexity remains.
If you load them into a temp table in a stored proc & return the contents, then you come out with one result set with all your records. If you prefer to do your iteration in PHP, you'll probably come up with a different data structure to hold the results.
Somewhere, you will select the records from Test. Somewhere you will iterate those records and build SQL statements and execute them.
Whether you do it in PHP or in SQL, the basic complexity remains.
If you load them into a temp table in a stored proc & return the contents, then you come out with one result set with all your records. If you prefer to do your iteration in PHP, you'll probably come up with a different data structure to hold the results.
Good luck, and please assign all points to Daniel as I was just voicing an alternative appraoch and not an answer.
Best of luck,
Joe
Best of luck,
Joe
Joe, that's a reasonable alternative ... and with some good reason behind it.
Whatever solution Marius implements, it needs some significant documentation! Any time you use one computer language to write statements in a computer language ... things get confusing. To my way of thinking they get moreso when language A is used to write instructions in language B. But at this point we're getting down to what each developer / team is comfortable with.
Marius, please see Joe gets a share in the points!
Whatever solution Marius implements, it needs some significant documentation! Any time you use one computer language to write statements in a computer language ... things get confusing. To my way of thinking they get moreso when language A is used to write instructions in language B. But at this point we're getting down to what each developer / team is comfortable with.
Marius, please see Joe gets a share in the points!
ASKER
I Absolutley agree. Will split the points accordingly. This is all part of a solo project I have been working on for a while. EE has provided a fantastic resource. Given your comments and suggestions, I will go away and review this one further. Thanks everyone.
After adding a row to Result (referencing both sample & test), you want to check whether Result_Value is greater than test_high.
If so, update test_high to that new value.
correct?