SQL query/update problem

I have a brain teaser, well for me anyway.

I have a database which stores species abundance in the following form:-

COUNTS TABLE SAMPLEID CODE COUNT

1112		A0023D	18
1112		A0021S	1
"		A0034F	2

SAMPLES TABLE SAMPLEID TOTCOUNT ....etc

112		21
113		15
114		11
115		78

Each sample in the SAMPLES table is related to a batch of counts in the COUNTS table by unique SAMPLEID. The CODE field is a species code from a taxonomic list. The TOTCOUNT field in SAMPLES is the sum(COUNT) from COUNTS.

My problem is that I need to check that the sum from COUNTS matches the TOTCOUNT field in SAMPLES where the SAMPLEID's match. Then give the user the option to update the ones that don't.

I created a VIEW which got the sum from COUNTS

# SCRIPT TO CREATE VIEW TOTCOMP */
 # To compare counts totals */  

create view TOTALCOMP(TOTAL, SAMPID) as
(select sum(COUNT), SAMPLEID from COUNTS group by COUNTS.SAMPLEID)

and then was able to join this with the totcount field with the following query:-

select TOTAL, TOTCOUNT, SAMPID, SITE, SAMPLENAME from TOTALCOMP, SAMPLES
where
TOTALCOM.SAMPID = SAMPLES.SAMPLEID
and TOTALCOMP.TOTAL != SAMPLES.TOTCOUNT

but, although this works adequately, it does not return any rows where one of the fields TOTAL or TOTCOUNT are blank. So, my questions are:-

1. Is there a way of returning the rows where a field is NULL?
2. Can this be done in a more efficient way, in a query without a VIEW?
3. How should I tackle the update problem?

Any suggestions would be gratefully received.

Thanks

Angus Beare
abeare_at_geog.ucl.ac.uk
UCL, London Received on Wed Aug 17 1994 - 00:33:09 CEST