sql

What follows is a description of 2 tables and some selects we ran against them, we think the results are not what they should be. Environment Online 5.0 on a SUN SPARC with sun OS 4.1.3 DBaccess Description of the query : Give a list of all assignments (opdracht), with their assignment number (onr), [ the vehicle number (vnr) ] and the number/identification of the goods (gnr) of which the largest (total) quantity (hoeveelheid) had to be loaded. =============== TABLES USED ======================= opdracht : ----------- onr cnr vnr datum uur_uit uur_in km ---- ---- ---- ---------- ------- ------ ------ o235 c003 v007 03/10/1993 09:15 11:47 78 o236 c001 v005 03/10/1993 08:57 11:54 92 o237 c002 v001 03/10/1993 09:23 11:36 83 o238 c006 v004 03/10/1993 09:08 12:31 128 o239 c002 v007 03/10/1993 13:16 15:27 65 o240 c001 v005 03/10/1993 13:38 15:06 38 o241 c004 v002 03/10/1993 13:17 17:34 150 o242 c002 v007 03/10/1993 16:23 18:05 42 te_leveren : ------------- onr adres gnr hoeveelheid ---- ------------------------- ---- ----------- o235 rozenlaan 23, eeklo g001 3 o235 rozenlaan 23, eeklo g008 3 o235 rozenlaan 23, eeklo g004 2 o235 rozenlaan 23, eeklo g005 1 o236 tuinstraat 8, zottegem g011 5 o236 tuinstraat 8, zottegem g013 3 o236 eedstraat 16, oudenaarde g011 2 o236 eedstraat 16, oudenaarde g006 1 o236 eedstraat 16, oudenaarde g009 1 o237 lindestraat 48, wevelgem g002 5 o237 lindestraat 48, wevelgem g007 5 o238 veldstraat 43, roeselare g003 10 o238 keistraat 4, torhout g005 2 o238 keistraat 4, torhout g006 1 o239 rubenslaan 2, maldegem g007 3 o239 rubenslaan 2, maldegem g008 2 o240 kerkplein 3, melle g001 1 o240 kerkplein 3, melle g009 1 o240 kerkplein 3, melle g011 2 o241 roze 24, brugge g004 3 o241 roze 24, brugge g005 1 o241 keistraat 4, torhout g006 1 o241 olmweg 104, diksmuide g004 2 o241 olmweg 104, diksmuide g001 2 o242 kerkstraat 9, lokeren g011 3 o242 kerkstraat 9, lokeren g013 2 o242 kerkstraat 9, lokeren g002 1 =============== FIRST QUERY ========================= select distinct opdracht.onr, vnr, gnr from opdracht, te_leveren where opdracht.onr = te_leveren.onr and gnr in (select gnr from te_leveren where onr = opdracht.onr group by gnr having sum(hoeveelheid) >= all (select sum(hoeveelheid) from te_leveren where onr = opdracht.onr group by gnr)) result : --------- onr vnr gnr ---- ---- ---- o235 v007 g001 o235 v007 g008 o236 v005 g011 -> o237 is missing o238 v004 g003 o239 v007 g008 o240 v005 g001 -> should not be in result o240 v005 g011 o241 v002 g001 o242 v007 g011 =============== SECOND QUERY ========================= select opdracht.onr, vnr, gnr from opdracht, te_leveren where opdracht.onr = te_leveren.onr group by opdracht.onr, vnr, gnr having sum(hoeveelheid) >= all (select sum(hoeveelheid) from te_leveren where onr = opdracht.onr group by gnr) result : --------- onr vnr gnr ---- ---- ---- o235 v007 g001 o235 v007 g008 o236 v005 g011 o236 v005 g013 -> should not be in result o237 v001 g002 o237 v001 g007 o238 v004 g003 o239 v007 g007 -> o240 is missing o241 v002 g004 o242 v007 g011 =============== THIRD QUERY ========================= select onr, gnr from te_leveren x group by onr, gnr having sum(hoeveelheid) >= all (select sum(hoeveelheid) from te_leveren where onr = x.onr group by gnr) result : --------- onr gnr ---- ---- o235 g001 o235 g008 o236 g011 o236 g013 -> should not be in result o237 g002 o237 g007 o238 g003 o239 g007 -> o240 is missing o241 g004 o242 g011 =============== FOURTH QUERY ========================= select distinct onr, gnr from te_leveren x where not exists (select gnr from te_leveren where onr = x.onr group by gnr having sum(hoeveelheid) > (select sum(hoeveelheid) from te_leveren where onr = x.onr and gnr = x.gnr)) result : --------- onr gnr ---- ---- o235 g001 -> a lot is missing !!! How should this behavior be explained and if things are indeed wrong is there a solution to the problem ? -------------------------------------------------------------------------------- Luc Verschraegen Phone: +32-(0)9-2644732 E-mail: Luc.Verschraegen@rug.ac.be Fax: +32-(0)9-2644994 -------------------------------------------------------------------------------