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