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 ... ;-).
--
