Having trouble with left join SQL in SQLite -

Having trouble with left join SQL in SQLite -

short background: have sqlite database, couple of gb in size , growing. contains bunch of simple tables. each table consists of 64-bit integer primary index field (tstamp) , value field (val). field tstamp long-int representation of date-time. tables have varying row-counts , variable content types, shouldn't matter. master table (tbinddate) holds total range of dates, has same primary index (tstamp) other tables, , holds human-readable date-time in val field. instance,

the master index table, named tbinddate:

tstamp val 634082688000000000 5/1/2010 0:00:00 634082691000000000 5/1/2010 0:05:00 634082694000000000 5/1/2010 0:10:00 634082697000000000 5/1/2010 0:15:00 etc etc

a sample table automation tag 6fi1.pv, named tb6fi1%pv:

tstamp val 634085793000000000 41.7 634085796000000000 42.83 634085799000000000 41.44 634085802000000000 40.43 634085805000000000 39.78 etc etc

getting info tables handled little vb.net program, , when new automation tag added capture list programme creates new table using automation tag name, , begins populating it. works real slick.

ok. i've started building tool getting info out of database. works great inner joins:

select [tbinddate].[val] 'timestamp',[tb6fi1%pv].[val] '6fi1.pv', [tb6fi34%pv].[val] '6fi34.pv',[tb6ai32%pv].[val] '6ai32.pv' [tbinddate],[tb6fi1%pv],[tb6fi34%pv],[tb6ai32%pv] [tbinddate].[tstamp]=[tb6fi1%pv].[tstamp] , [tbinddate].[tstamp]=[tb6fi34%pv].[tstamp] , [tbinddate].[tstamp]=[tb6ai32%pv].[tstamp];

this returns:

timestamp 6fi1.pv 6fi34.pv 6ai32.pv 1/1/2013 0:00:00 42.4679 1.499 0.8439 1/1/2013 0:05:00 40.3628 1.5048 0.8435 1/1/2013 0:10:00 38.2652 1.5028 0.8436 1/1/2013 0:15:00 37.8582 1.5029 0.8436

yay! :)

i've gotten averaging , time-interval queries working.

however, because tag info not available dates, i'd create alternative list dates in master index if of tag tables not have matching data.

a select query left outer join, in other words. knows that. info might like:

timestamp 6fi1.pv 6fi34.pv 6ai32.pv 1/1/2013 0:00:00 42.4679 1.499 null 1/1/2013 0:05:00 40.3628 1.5048 null 1/1/2013 0:10:00 38.2652 null null 1/1/2013 0:15:00 37.8582 null 0.8436

trouble is, none of sql i've tried has worked. here's 1 didn't go:

select [tbinddate].[val] 'timestamp',[tb6fi1%pv].[val] '6fi1.pv', [tb6fi34%pv].[val] '6fi34.pv' [tbinddate],[tb6fi1%pv],[tb6fi34%pv] left bring together [tbinddate] on [tbinddate].[tstamp]=[tb6fi1%pv].[tstamp] left bring together [tbinddate] on [tbinddate].[tstamp]=[tb6fi34%pv].[tstamp];

the error "sql error or missing database, ambiguous column name: tbinddate.val"

i've tried copying syntax several examples, none same , attempts fail.

am doing aliases wrong? square brackets accommodate special characters in table names? i'm finish sql beginner, don't hold advice.

it looks problem you're trying bring together [tbinddate] several times. seek this:

select [tbinddate].[val] 'timestamp',[tb6fi1%pv].[val] '6fi1.pv', [tb6fi34%pv].[val] '6fi34.pv' [tbinddate] left bring together [tb6fi1%pv] on [tbinddate].[tstamp]=[tb6fi1%pv].[tstamp] left bring together [tb6fi34%pv] on [tbinddate].[tstamp]=[tb6fi34%pv].[tstamp];

sql sqlite