In epistula a Robert L Cochran, die horaque Thu, Oct 06, 2005 at 10:56:03PM -0400: > Hi -- > > I'm inexperienced with joining tables and need help. > > I'm using SQLite v3.2.7 and the tables discussed below are part of an > SQLite database. > > There are 2 tables: A contains some text columns and an integer primary > key. B contains an integer primary key and a LONGBLOB column to hold > image data like jpegs. The primary key in B should match one and only > one primary key in A. At this point, table B has 2 rows and needs to be > updated with several hundred more. > I want to craft an SQL query that will return every row in A having a > primary key that does not match the keys in the rows in B. Then display > these rows on a web page form. I eventually tried doing a left outer > join of A and B: Does
SELECT sa.dbsource, sa.signid, sa.recid, sa.majorlvl, sa.lvl6 FROM sampsign AS sa LEFT OUTER JOIN sgnphoto AS j ON sa.recid = j.recid WHERE sa.recid IS NULL AND sa.dbsource = 'Smith' AND sa.majorlvl = 'photograph' do, what you want? For explanation: Outer joins return the same data set as inner joins with the following rows added: - For left outer joins: Every row from the left table that did not match any row in the right table. Since there is no matchin data row in the left table, these fields are set to NULL. - Same goes for right outer joins but with tables roles reversed. - full outer joins return the combination of both left and right outer joins. So, if you want all the data in your right table that did not match anything on your left table, just check, if the corresponding key column (use those, since primary keys cannot be null) is null. Cheers, Peter PS.: "= NULL" does not work "IS NULL" does work. And don't laugh, last time I've seen this error was in code from an experienced database programmer ... ;-). --