sqlite: join syntax creates "no such table" error

[shutat]

I'm trying to conditionally add a table to a select query using the join, but I always get an "no such table"  error.  I'm not at all sure if the syntax is correct, so I decided to ask. 

 

I'm using the sqlite C API interface and my tables and query were constructed using sprintf, so please ignore any %d, %s that may appear

CREATE TABLE IF NOT EXISTS tbl_master
(
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     volume TEXT(16) UNIQUE NOT NULL DEFAULT '',
     note   TEXT(%d) NOT NULL DEFAULT '',
     items INTEGER NOT NULL DEFAULT 0
);
 
CREATE TABLE IF NOT EXISTS tbl_file
(
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      volume_key TEXT(16) NOT NULL DEFAULT '',
      name TEXT(%d),
      size TEXT(20),
      type TEXT(4),
      path TEXT(%d),
      file_id INTEGER
);
 
CREATE TABLE IF NOT EXISTS tbl_hash
(
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     hash TEXT(33) NOT NULL DEFAULT '',
     volume_key TEXT(16) NOT NULL DEFAULT '',
     file_key INTEGER NOT NULL DEFAULT 0
);
 
CREATE TABLE IF NOT EXISTS tbl_media
(
       id INTEGER PRIMARY KEY AUTOINCREMENT,
       runtime TEXT(10) NOT NULL DEFAULT '',
       frame TEXT(12) NOT NULL DEFAULT '',
       type_key TEXT(4) NOT NULL DEFAULT '',
       hash_key TEXT(33) NOT NULL DEFAULT '',
       volume_key TEXT(16) NOT NULL DEFAULT '',
       file_key INTEGER NOT NULL DEFAULT 0
);

I milled over a few JOIN tutorials, but I'm still unclear on the exact usage of JOIN; I'm trying to add the media table if the file type is a vid, snd, or pix, but I need file information regardless.  I've tried various flavors of the following query, but each time I get the table doesn't exist error.  Selects, deletes, updates, inserts work on all tables, so I'm guessing my syntax is wrong with the JOIN.

SELECT tbl_file.*, tbl_hash.hash
  FROM tbl_file AS f, tbl_hash AS h
  LEFT OUTER JOIN tbl_media AS m
  ON  ((f.type='VID' OR f.type='SND' OR f.type='PIX')
  AND m.file_key=f.file_id)
  WHERE ((f.volume_key=tbl_master.volume
  AND (h.volume_key=tbl_master.volume AND h.file_key=f.file_id)))
  ORDER BY f.path ASC;

Any ideas on how to pull off what I'm trying to do would be greatly appreciated.  Thank you for your time.

 

 

 

[mac_gyver]

afai can recall, when you assign an alias name (your f, h, and m), you MUST use the alias name for all table references. your SELECT term must also use the aliases, not the full table names.

[shutat]

I had no idea; you don't know how frustrating that was to see no such table when I knew it wasn't true.  Thank you.

Edited May 16, 2014 by shutat