CODEWITHSUNDEEP
sqlandroiddatabasesqlite
brux
brux

Reputation: 3230

sqlite select query

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.

Upvotes: 1

Views: 1358

Answers (1)

Aishwar
Aishwar

Reputation: 9714

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');

Upvotes: 2

Related Questions