Re: SQL query/update problem

In article <1994Aug16.102210.22389_at_ucl.ac.uk> ucfaabe_at_ucl.ac.uk (Mr Angus Beare) writes:
>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

 You could try this

select s.sampleid from samples s
where nvl(totcount,0) !=
(select nvl(sum(nvl(count_tot,0)),0) from counts c where c.sampleid = s.sampleid)

Regards

Ramesh Krishnamurthy Received on Thu Aug 18 1994 - 23:13:40 CEST