• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

[MySQL/Hibernate] joining 2 resultsets and adding a new column based on condition

 
Ranch Hand
Posts: 541
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 :-)



 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Why should you get only a list of bad sensors with an outer join?

Left outer join contains all rows from the left table. Columns from the right table will contain the proper value from the right table's rows (for rows existing in the right table), or NULL when the row doesn't exist in the right table. If you use a non-null column from the right table, you can be sure that non-null values denote good sensors and NULL denotes bad sensors.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic