Thanks very much for your help, Peter. Here is a variation that seems to
be working for me:
select sa.lg_abbr, sa.dbsource, sa.signid, sa.recid, sa.majorlvl,
sa.lvl6, j.imgfn from sampsign as sa left outer join sgnphoto as j on
sa.recid = j.recid where sa.dbsource = 'Smith' and sa.majorlvl =
'photograph';
Notice that I added j.imgfn to the join. As I think you are trying to
tell me below, every record from the left table which does not match on
the right table is going to have NULL returned in the column that is
j.imgfn. All I need to do is test for IS NULL in that column of the
result set, and I have what I want! The rows that did not match.
So, being new to working with left joins, I didn't think to test for IS
NULL in the appropriate column to detect a non-matching record. I have
the correction working now, and I can go on to the next step in the
processing.
By the way, SQLite 3.2.7 does not support RIGHT or FULL joins.
Bob
Peter Wullinger wrote:
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 ... ;-).
