SQL-QUERY: How to find quantity=0?
From: Arkusa GbR <arkusa_at_t-online.de>
Date: 1996/10/29
Message-ID: <555sbs$2t7_at_news00.btx.dtag.de>#1/1
) order by count(anr);
12 100
11 300
:
12 100
11 300
:
Date: 1996/10/29
Message-ID: <555sbs$2t7_at_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 CET