[Solved] Base Query to previous date

[emre]

Hi Everyone,
I'am quite new in this forum and trying to create a basic form. In my case, I need to query -1 day on the table 2 by refering to table 1 where ID=0. Is it possible using something like following. I am sorry for my poor explanation If I do not use right terms.

SELECT "ID", "Date" FROM "Table1" WHERE "Date" = ( SELECT "Date" FROM "Table2" WHERE "ID" = 0 ) -1

thank you

[Villeroy]

If you happen to use the embedded HSQL which is documented here: http://www.hsqldb.org/doc/1.8/guide/ch09.html

Code: Select all

SELECT "T1".* 
FROM "T1","T2" 
WHERE "T2"."ID"=0
  AND DATEDIFF('day', "T1"."Date", "T2"."Date") = -1 

DATEDIFF returns an integer. Note that HSQL 1.8 is missing a DATEADD function. In HSQL 2 DATEADD('day', 13, "Date") returns the dates 13 days ahead of the "Date" field.

[emre]

That resolved my problem. Thank you for your help.