Left Outer Joins

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Left Outer Joins

Robert L Cochran
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:

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.dbsource = 'Smith' and sa.majorlvl = 'photograph';

The result set comes extremely close to what I want. The trouble is, it
includes the 2 rows from A which match the rows in B. I'd like to get
rid of them and see only the non-matching rows.

Thanks a lot for your help!

Bob Cochran
Greenbelt, Maryland, USA

Reply | Threaded
Open this post in threaded view
|

Re: Left Outer Joins

Brett Wilson
I believe saying "GROUP BY a.primarykey" clause at the end of your
query will do what you want. It's explained in
http://sqlite.org/lang_select.html There might also be better ways of
doing it, but I'm not an expert.

Brett

On 10/6/05, Robert L Cochran <[hidden email]> wrote:

> 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:
>
> 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.dbsource = 'Smith' and sa.majorlvl = 'photograph';
>
> The result set comes extremely close to what I want. The trouble is, it
> includes the 2 rows from A which match the rows in B. I'd like to get
> rid of them and see only the non-matching rows.
>
> Thanks a lot for your help!
>
> Bob Cochran
> Greenbelt, Maryland, USA
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Left Outer Joins

Mark de Vries
On Thu, 6 Oct 2005, Brett Wilson wrote:

> I believe saying "GROUP BY a.primarykey" clause at the end of your
> query will do what you want. It's explained in

I would try something like:

select sa.dbsource, sa.signid, sa.recid, sa.majorlvl, sa.lvl6
from sampsign sa
where sa.dbsource = 'Smith'
and sa.majorlvl = 'photograph'
and not exists (select j.recid from sgnphoto j where j.recid=sa.recid)

> http://sqlite.org/lang_select.html There might also be better ways of
> doing it, but I'm not an expert.
>
> Brett
>
> On 10/6/05, Robert L Cochran <[hidden email]> wrote:
> > Hi --
> >
> > 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
> > I'm inexperienced with joining tables and need help.
> >
> > these rows on a web page form. I eventually tried doing a left outer
> > join of A and B:
> >
> > 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.dbsource = 'Smith' and sa.majorlvl = 'photograph';
> >
> > The result set comes extremely close to what I want. The trouble is, it
> > includes the 2 rows from A which match the rows in B. I'd like to get
> > rid of them and see only the non-matching rows.
> >
> > Thanks a lot for your help!
> >
> > Bob Cochran
> > Greenbelt, Maryland, USA
> >
> >
>


Regards,
Mark

Reply | Threaded
Open this post in threaded view
|

Re: Left Outer Joins

Jay Sprenkle
In reply to this post by Robert L Cochran
On 10/6/05, Robert L Cochran <[hidden email]> wrote:
>
> 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:


select * from a
where a.id <http://a.id> not in ( select distinct id from b )

D:\temp\convention>sqlite3
SQLite version 3.0.8
Enter ".help" for instructions
sqlite> create table a ( id integer, stuff text );
sqlite> create table b ( id integer, path text );
sqlite> insert into a(id,stuff) values(1,'one');
sqlite> insert into a(id,stuff) values(2,'two');
sqlite> insert into b values(1,'one');
sqlite> select * from a where a.id <http://a.id> not in ( select distinct id
from b );
2|two
sqlite>


You might also consider NOT saving the image data in the database.
I've always found it better to save a path or URL in the database and store
the image in the file system. (Unless you're searching for things
in the image data itself and not just searching for an image using other
fields as keys)
Reply | Threaded
Open this post in threaded view
|

Re: Left Outer Joins

Peter Wullinger
In reply to this post by Robert L Cochran
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 ... ;-).
 
--
Reply | Threaded
Open this post in threaded view
|

Re: Left Outer Joins

Robert L Cochran
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 ... ;-).
>
>  
>

Reply | Threaded
Open this post in threaded view
|

Re: Left Outer Joins

Robert L Cochran
In reply to this post by Jay Sprenkle
Jay Sprenkle wrote:

>You might also consider NOT saving the image data in the database.
>I've always found it better to save a path or URL in the database and store
>the image in the file system. (Unless you're searching for things
>in the image data itself and not just searching for an image using other
>fields as keys)
>
>  
>
Thanks Jay. I think I found another solution that works -- see my post
in reply to Peter. As to not saving the image data in the database, that
is what we are doing on the production server. The images are in the web
server's document root. This exercise is for my benefit, to play with
multimedia databases and learn PHP and web application building. It is
tough work for me, but I'm learning and I know I have a long way to go.

Bob