SQL-QUERY: How to find quantity=0?

From: Arkusa GbR <arkusa_at_t-online.de>
Date: 1996/10/29
Message-ID: <555sbs$2t7@news00.btx.dtag.de>#1/1

QUERY-PROBLEM: I have a table which includes rows of a store. I have to find out the quantity of articles stored in this store. How can I find out which row does not contain any representative of the given article?

For example:

row 11 contains the article with the number 4711 300 times,
row 12                                           100 times,
row 13                                           500 times,
row 14                                             0 times,
row 15                                            70 times:


:

row 19                                            20 times

The following select-statement:

SELECT GNR,count(anr) from STORE where (ANR = '4711') group by gnr having count(anr) in
(select (count(anr)) from STORE

      where  (anr='4711') and 
            (GNR in (Select substr(first.BPOS,2,1) from RL first, RL 
second  
                       where (first.MFSTAT=1)  and 
                             (second.MFSTAT=1) and 
                             (((substr(first.BPOS,1,2) in ('11','12')) 
and
                              (second.BPOS = '021VW_13'))
                             or
                             ((substr(first.BPOS,1,2) in

('13','14','15','16','17','18','19')) a

                              (second.BPOS = '021VW_21')))
                    ) 
            )
     group by gnr

) order by count(anr);

checks in the sub-select for Table RL whether the row is reachable (1 check for the transport
to the row and one check for the fixed elevator in the row to be ok).

The result I get is:

19     20 
15     70

12 100
11 300
:

What I need to get is:

13      0
19     20 
15     70

12 100
11 300
:

I have tried a lot with not exists or <= any, but I can´t get it.

Has anyone an Idea?

Thanks a lot

Manfred Tischendorf

      /\
     / a\       Manfred Tischendorf
    / s  \
   / u    \     Bruehlstr. 2
  / k      \    D 74379 Ingersheim
 / r        \   Deutschland / Germany
 \a         /
  \        /    Tel.:    +49 7142 9800-27 / +49 171 6821030
   \      /     Fax:     +49 7142 9800-29
    \____/      E-Mail:  tischendorf_at_arkusa.de
     \  /       WWW:     http://www.arkusa.de/
      \/
       -----------------------------------------------------------

Received on Tue Oct 29 1996 - 00:00:00 CST