mardi 15 décembre 2015

How do you use WHERE BETWEEN clause in SQLite, when the values come from multiple tables?

I have the following tables (full schema included, however only the TIME values are necessary:

"POINTS" TABLE

     TSTAMP,                 LAT,   LON,     HDOP,    ALT,     FILE,        EUID
    "2015-12-15T20:29:54"   "0.0"   "0.0"   "99.99" "NULL"  "L151400.GPS"   "080D55"
    "2015-12-15T20:29:55"   "0.0"   "0.0"   "99.99" "NULL"  "L151400.GPS"   "080D55"
    "2015-12-15T20:29:56"   "0.0"   "0.0"   "99.99" "NULL"  "L151400.GPS"   "080D55"

"AUDIO" TABLE

START,                  STOP,                    NCHANNELS,     SAMPWIDTH,  FRAMERATE, NFRAMES, COMPTYPE, COMPNAME, FILE, FILESIZE, EUID
"2015-12-15T22:37:00"   "2015-12-15T22:39:53"   "1.0"   "2.0"   "16000.0"   "2777088.0" "NONE"  "not compressed"    "static/uploads/WAV/L151637.WAV"    "5554688.0"
"2015-12-15T22:27:00"   "2015-12-15T22:37:00"   "1.0"   "2.0"   "16000.0"   "9601024.0" "NONE"  "not compressed"    "static/uploads/WAV/L151627.WAV"    "19202560.0"
"2015-12-15T22:17:00"   "2015-12-15T22:27:00"   "1.0"   "2.0"   "16000.0"   "9601024.0" "NONE"  "not compressed"    "static/uploads/WAV/L151617.WAV"    "19202560.0"

I need to find each record in the POINTS table that has a TSTAMP which falls between any START / STOP pair of the AUDIO table.

Thus far I've tried iterations of the following:

SELECT FILE FROM POINTS 
WHERE (
TSTAMP
BETWEEN (SELECT START FROM AUDIO) AND 
(SELECT STOP FROM AUDIO)
);

Which returns 0. I know this data lines up, and given the right search statement I should have multiple records returned. However, I can't seem to find any references on how to address pulling columns from multiple tables in one search statement.

I know I could cluge this together in Python, but it seems like a normal job for SQL in general.

Still getting used to SQL, so please be explicit in your answers. Thanks

Aucun commentaire:

Enregistrer un commentaire