Scenario : We have sensors that senses data on given interval and a
java program that saves the data back on the server through webservices. At the end of each day I need to find out if there are any bad sensors (if I do not see sensor reading in database.)
Approach 1 :
- Retrieve total list of sensors from db.
- Retrieve list of sensors from which you heard back from db.
- In java, compare the list and it is done.
No of queries : 2
Approach 2 :
Write a single query that does the left outer join between 2 resultsets (mentioned in approach 1). but this will give me list about ONLY bad sensors. What I want is, list of all sensors with a non-existent column in resultset that tells me about good/bad sensor.
Result set:
Sensor Id condition(non-existent column)
11111111 bad
22222222 good
I know it is possible to add a new column in resultset based on condition but I can not recall it now. Is it possible to write a single query (later I need to convert to HQL) to achieve this? Should I fall back to approach 1?
Thank you and have a happy new year :-)