Databases
StackOverflow2013
Postsdbo
POHaving trouble with left join SQL in SQLite
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHaving trouble with left join SQL in SQLite
    primarykey
    data
    text
    <p>Short background: I have an SQLite database, a couple of gb in size and growing. It contains a bunch of very simple tables. Each table consists of a 64-bit integer primary index field (TStamp) and a value field (Val). The field TStamp is actually an long-int representation of a date-time. The tables have widely varying row-counts and somewhat variable content types, but that shouldn't matter. A master table (tbIndDate) holds a full range of dates, has the same primary index (TStamp) as the other tables, and holds human-readable date-time in its Val field. For instance,</p> <p>The master index table, named tbIndDate:</p> <pre><code>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 </code></pre> <p>A sample table for automation tag 6FI1.PV, named tb6FI1%PV:</p> <pre><code>TStamp Val 634085793000000000 41.7 634085796000000000 42.83 634085799000000000 41.44 634085802000000000 40.43 634085805000000000 39.78 etc etc </code></pre> <p>Getting data into the tables is handled by a little vb.net program, and when a new automation tag is added to the capture list then the program creates a new table using the automation tag name, and begins populating it. That all works real slick.</p> <p>OK. I've started building a tool for getting data out of the database. It works great for inner joins:</p> <pre><code>SELECT [tbIndDate].[Val] AS 'Timestamp',[tb6FI1%PV].[Val] AS '6FI1.PV', [tb6FI34%PV].[Val] AS '6FI34.PV',[tb6AI32%PV].[Val] AS '6AI32.PV' FROM [tbIndDate],[tb6FI1%PV],[tb6FI34%PV],[tb6AI32%PV] WHERE [tbIndDate].[TStamp]=[tb6FI1%PV].[TStamp] AND [tbIndDate].[TStamp]=[tb6FI34%PV].[TStamp] AND [tbIndDate].[TStamp]=[tb6AI32%PV].[TStamp]; </code></pre> <p>This returns:</p> <pre><code>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 </code></pre> <p>Yay! :)</p> <p>I've also gotten some averaging and time-interval queries working.</p> <p>However, because tag data is not all available for all dates, I'd like to create the option to list all dates in the master index even if some of the tag tables do not have matching data.</p> <p>A SELECT query with a left outer join, in other words. Everyone knows that. The data might look like:</p> <pre><code>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 </code></pre> <p>Trouble is, none of the SQL I've tried has worked. Here's one that didn't go:</p> <pre><code>SELECT [tbIndDate].[Val] AS 'Timestamp',[tb6FI1%PV].[Val] AS '6FI1.PV', [tb6FI34%PV].[Val] AS '6FI34.PV' FROM [tbIndDate],[tb6FI1%PV],[tb6FI34%PV] LEFT JOIN [tbIndDate] ON [tbIndDate].[TStamp]=[tb6FI1%PV].[TStamp] LEFT JOIN [tbIndDate] ON [tbIndDate].[TStamp]=[tb6FI34%PV].[TStamp]; </code></pre> <p>The error was "SQL error or missing database, ambiguous column name: tbIndDate.Val"</p> <p>I've tried copying the syntax from several examples, but none are exactly the same and my attempts fail.</p> <p>Am I doing the aliases wrong? The square brackets to accommodate special characters in table names? I'm a complete SQL beginner, so don't hold back with the advice.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USD In Eugene
    1. USD In Eugene
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHaving trouble with left join SQL in SQLite
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWelcome to Stack Overflow! This seems like a good question, but it is a bit long, which might turn away some answerers. I've removed some blank lines, but perhaps some of the information about what already works isn't necessary, or could be shortened?
      singulars
      1. POHaving trouble with left join SQL in SQLite
      1. USughoavgfhw
    2. COThanks for the welcome -- I edited out some verbosity. Didn't want my first post to be one of those "not enough info" ones! -- Don
      singulars
      1. POHaving trouble with left join SQL in SQLite
      1. USD In Eugene
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload