CODEWITHSUNDEEP
sqlandroidsqlitecursor
Holm
Holm

Reputation: 982

SQLite query three tables

I'm working on an Android app that will display some information from a sqlite database in a listview. I need some help sorting out my query.

The database looks like this: 

[monitors] 1 --- <has> --- * [results] 1 --- <has> --- * [steps]

Table monitors has columns: _id | warning_threshold | alarm_threshold | monitor_name
Table results  has columns: _id | monitor_id        | timestamp       | test_info
Table steps    has columns: _id | result_id         | correct         | response_time

I'm trying to make a query that would return:
1) All rows & columns from the monitors table.
2) The newest test_info for each monitor from the results table.
3) Count the number of correct = true for each result from the steps table.

The returned cursor should look something like this:

_id | monitor_name | warning_threshold | alarm_threshold | test_info   | correct_count
1   | 'hugo'       | 1000              | 1500            | 'some info' | 7
2   | 'kurt'       | 800               | 1200            | 'info.....' | 5

My query:

SELECT * FROM
  (SELECT monitors._id AS _id,
  monitors.monitor_name AS monitor_name,
  monitors.warning_threshold AS warning_threshold,
  monitors.alarm_threshold AS alarm_threshold,
  results.test_info AS test_info
  FROM monitors
  LEFT JOIN results
  ON monitors._id = results.monitor_id
  ORDER BY results.timestamp ASC) AS inner
GROUP BY inner._id;

I almost got it working. I am able to get the info from monitors and results, I still need to get the correct_count. Any help with sorting out this query would be greatly appreciated.

Upvotes: 1

Views: 584

Answers (2)

MPelletier
MPelletier

Reputation: 16697

This is my approach, using a combination of Left Joins, sub queries, and correlated subqueries:

SELECT monitors._id AS _id,
 monitors.monitor_name AS monitor_name,
 monitors.warning_threshold AS warning_threshold,
 monitors.alarm_threshold AS alarm_threshold,
 LastResults.test_info AS test_info,
 COUNT(CorrectSteps._id) AS correct_count
FROM monitors
LEFT JOIN 
 (SELECT * FROM results as r1 where timestamp =
   (SELECT Max(r2.timestamp) FROM results AS r2 WHERE r1.monitor_id=r2.monitor_id)) LastResults
ON monitors._id = LastResults.monitor_id
LEFT JOIN 
 (SELECT * FROM steps WHERE correct = 'true') CorrectSteps
ON LastResults._id = CorrectSteps.result_id
GROUP BY monitors._id;

Upvotes: 2

Jason L.
Jason L.

Reputation: 2484

Something like this should work. I haven't been able to test it out but hopefully it will at least get you started. Note that this query is not even close to optimized. Wrote it quickly during my lunch :)

SELECT m._id,
       m.monitor_name,
       m.warning_threshold,
       m.alarm_threshold,
       (SELECT r.test_info
          FROM results r
         WHERE r.monitor_id = m._id
         ORDER BY r.timestamp ASC
         LIMIT 1) as 'test_info',
       (SELECT COUNT(_id)
          FROM steps s
         WHERE s.result_id IN (SELECT _id FROM results WHERE monitor_id = m._id)
           AND s.correct = 'true') as 'correct_count'
  FROM monitor m

Upvotes: 1

Related Questions