Re: SQL Help

In article <1993Feb17.200813.28041_at_netnews.whoi.edu> you write:
>help!
>
>I have 2 tables, let's call them tow and spp. I have a one to many
>relationship between the two. I have assigned a doc number to each tow
>record. This doc number is the means by which the spp data is tied back to
>the tow data. What I am trying to accomplish is this: I want to take all
>the data from the spp table, and pull 2 other columns (let's call those
>columns tow.trip and tow.year) from the tow table where tow.doc and spp.doc
>are equal. For some reason when rows are inserted into the test table, I
>am getting 10 rows less that what exists in the spp table. I checked to see
>if there were any doc numbers that didn't match, and came up with nothing. I
>can't figure out what's wrong. Here's my code.
>
>insert into spp_test
> select spp.doc,
> spp.cntownum,
> tow.trip,
> tow.year,
> spp.sampled,
> spp.nafospp,
> spp.pcodealp,
> spp.catchwt
> from tow, spp
> where tow.doc = spp.doc
> and tow.cntownum = spp.cntownum;
>
>To find out if there were any doc numbers that didn't match, I tried:
>
>select * from spp_test
> where doc not in (select doc
> from spp);
>
>This returned 0 rows. Any help would be appreciated. Don't be too harsh,
>I'm learning!

Well, there's a couple of possibilities. Unfortunately, NOT IN does no take into account NULLS, so your test query would not work. I'll bet that if you run the following query, you will find rows in your spp table that do not have a matching ID pair in the tow table:

SELECT * from spp
WHERE (spp.doc, spp.cntownum) NOT IN
 (SELECT doc, cntownum FROM tpp)
OR (spp.doc IS NULL OR spp.cntownum IS NULL);

[actually, there's probably a simpler query, but this one will get the job done]

Mark

PS. I tried to send you this via email, but it bounced. Received on Thu Feb 18 1993 - 17:57:42 CET