I'm trying to solve the following problem in SQLite: Items are being produced with a short, fixed shelf life. Say 50 days. Item can be collected every two weeks, where each item must be registered a week before.
I am trying to create a query where I can list current items and the latest day they can be registered/collected. Given the tables: CREATE TABLE "items" ( "id" INTEGER PRIMARY KEY, "prod_code" TEXT UNIQUE, "creation_date" DATE, "collection_id" INTEGER REFERENCES "collections"("id"), -- for when it's been booked for collection ) CREATE TABLE "collections" ( "id" INTEGER PRIMARY KEY, "book_in_date" DATE, "collection_date" DATE UNIQUE ) The result I'm after is: id, prod_code, creation_date, last_book_in_date, last_collection_date Where the final two columns are from the collection which is the farthest in the future, but still within the 50-day period from creation. I think this sort of problem would require a subquery, but I can't seem to pass the creation_date of an item to a subquery for collections. Should I instead be processing this in the application rather than database? Can anyone point me in the right direction for how to approach this kind of problem? Thank you, Ben _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users