I’m certain I’ve done this sort of thing before, but I can’t dredge up the right sql-fu to get it working right. Let’s say I have 2 tables: car, and oil_change.
car has a uuid to uniquely identify rows.
oil_change has its own uuid, as well as a car_uuid.
Both tables have a datetime_created column with a timestamp for when the row was created.
A car may have multiple oil_change records.
Let’s say I want to find all the cars that have had their first oil change in the last n months. A car can get added to the database independently of an oil_change record.
The closest I’m coming is:
select
c.uuid, min(o.datetime_created)
from
car c, oil_change c
where
where c.uuid = o.car_uuid
group by c.uuid
order by o.datetime_created
This gets me each car, and the date of their first oil change, but does not limit it to only return cars that had their first oil change after a certain date… figuring out how to further limit the recordset in this way is what is turning my brain inside out. Any attempts to limit the records by the date of the oil change simply omit earlier changes, which can return cars that have had an oil change since the last date, even though it may have been their 3rd oil change.
I need only cars that had their first after a specified date. They can have had several since the first, but the first must have occurred after the date requested.
I can always go with the query I’m using and then loop through the recordset looking for records with a date newer than the one I care about, but it sure seems like limiting it in the database query would be much more efficient.
select cc.uuid,oo.datetime_created
from car cc, oil_change oo
where cc.uuid = oo.car_uuid
and (select min(o2.datetime_created)
from oil_change o2
where cc.uuid=o2.car_uuid)>SOMEDATE
Totally off the top of my head… but I Think that is what you want…
The 3-months ago is a variable for your prepared statement, sql date/time math, whatever works for your sql dialect.
Also, another way of writing it is:
SELECT
c.uuid,
MIN(o.datetime_created) AS datetime_first_oil_change
FROM
car AS c
INNER JOIN oil_change AS c on (o.car_uuid=c.uuid)
GROUP BY c.uuid
HAVING MIN(o.datetime_created) > 3-months-ago
ORDER BY o.datetime_created
INNER JOIN’s are generally considered a better practice than joining in the WHERE clause. It keeps the join criteria in a central place, easy to add new tables and also easier to migrate to OUTER JOIN’s when necessary.
BTW, some SQL dialects will allow you to do (with the above) HAVING datetime_first_oil_change > 3-months-ago, i.e. instead of duplicating MIN()…
Thanks both of you! I kept thinking that Having was not allowed in SQLite, but after consulting docs, I see that it is. One slight correction to @Jeremy Cowgar’s response: HAVING must come before ORDER BY, so your query becomes:
SELECT
c.uuid,
MIN(o.datetime_created) AS datetime_first_oil_change
FROM
car AS c
INNER JOIN oil_change AS c on (o.car_uuid=c.uuid)
GROUP BY c.uuid
HAVING MIN(o.datetime_created) > 3-months-ago
ORDER BY o.datetime_created
Which, at first glance, seems to be returning the correct data.
