sqlite select query

Question

I am working in Android with SQLite.

My db has 2 tables:

Table 1: cars

_id(int,pri-key)
reg(text)
type(text)

Table 2: jobs

_id(int,foreign-key)
date(date)

I need a sqlite statment which will get me all cars which have NOT had a job in the past 3 weeks. Iam actually porting the app from c#, and the statment I use for this (in MySQL) is

SELECT c.id, c.reg, c.type FROM buses c WHERE NOT EXISTS (SELECT NULL FROM jobs j WHERE j.id = c.id AND j.date >= CURRENT_DATE - INTERVAL 21 DAY

But the SqliteDataBase Object I am working with in android takes a different format, How would I run this query?

Thanks in advance.

Aishwar · Accepted Answer · 2010-11-22 22:26:40Z

2

I would try something like this:

SELECT * from cars A LEFT OUTER JOIN jobs B on A._id = B._id WHERE B._id IS NULL OR B.date < date('now', '-21 days');

The LEFT OUTER JOIN, ensures all values from the cars table are shown in the output (including the ones that don't match the join criteria - i.e. ones that don't have an entry in the jobs table). The WHERE criteria, filters for either, ones that don't have an entry in the jobs table (B._id IS NULL) or ones that are more than 21 days old B.date < date('now', '-21 days')

Of course I am assuming, there will be only 1 entry on the Jobs table for each car. If there will be more, you probably want to use MAX to get the latest date.

WORKING SOLUTION: SELECT * from cars A LEFT OUTER JOIN jobs B on A._id = B._id GROUP BY A._id HAVING B._id IS NULL OR MAX(B.date) < date('now', '-21 days');

edited Nov 22, 2010 at 22:26

answered Nov 22, 2010 at 2:04

Aishwar

9,75412 gold badges63 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

brux Over a year ago

I also tried running the query from within sqlite3 shell and i get "error near WHERE syntax error

Aishwar Over a year ago

@brux: I don't have a sqlite database here now to verify this works. But the updated SQL statement should be valid. Can you try that out?

brux Over a year ago

"Error: misuse of aggregate: MAX(B.date)"

Aishwar Over a year ago

@brux: Sorry about this. I should have checked it the first time it self.

Collectives™ on Stack Overflow

sqlite select query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related