recherche solution requête sql

Version imprimable

Bonjour,

Voilà j'ai une requête à faire entre deux tables, l'objectif étant de n'afficher les lignes de la table 1 que si la somme d'une colonne de la table 2 est inférieur à un champ fixé dans la table 1

Soit en plus clair :

TABLE 1
Idtable1 1
Limite 500

TABLE 2
IDtable2 1
DATE 20081206
VAL 1
IDtable1 1

IDtable2 2
DATE 20081206
VAL 1
IDtable1 1

Voici ma requête (fausse sans doute) :
Code:

1 2 3 4 5 6 7 8 select * from table1 where NOT EXISTS (select sum(VAL) as valeur from table2 where table2.idtable1=table1.idtable1 and date=CURDATE() and valeur<table1.limite)
L'objectif est d'afficher tous les éléments de la table1 pour lesquels la somme du champ VAL de la table 2 est inférieur au champ LIMITE de la table 1 et est égale à la date du jour. On joindra les deux tables sur l'id de la table1
Si on ne trouve pas d'élément dans la table 2, la ligne de la table 1 doit sortir
L'idée est de gérer une limitation définit par la table 1 par exemple limité à 500
On fait la somme des correspondances dans la table 2 sur la colonne VAL et si c'est inférieur on affiche la ligne de la table 1 sinon on ne l'affiche pas
le NOT EXISTS ne fonctionnera sans doute pas ici
Vous pensez que celà pourrait marcher ?

07/12/2008, 10h28
soazig
Bonjour,

Est-ce que cette requête répond à ta question
Code:

1 2 3 4 5 SELECT Idtable1, limite from TABLE1 left outer join table2 on TABLE1.Idtable1=TABLE1.Idtable2 and TABLE2.DATE2=CURDATE() having sum(COALESCE(VAL))<table1.limite
Mais tu n'es pas très précis, tu ne dis pas quel est ton sgbd.
Et je n'ai pas très bien compris où ce situait la date dans le problème.
a+
Soazig

alors pour te répondre HAVING est en effet la solution dans mon cas
J'utilise MYSQL
Par contre ou est le where dans ta requête ?
Tu mets çà dans le code :

Code:

AND TABLE2.DATE2=CURDATE()

Ce n'est pas :

Code:

WHERE TABLE2.DATE2=CURDATE() ???

Voici ma requête de départ :
Code:

1 2 3 4 5 6 7 8 SELECT idpub,type, quantiteutil, quantite, etat, adulte, gaineuros, lien, banniere, hauteur, largeur FROM TABLE1 WHERE type='clic' and quantiteutil<quantite and etat='ACTIF' and NOT EXISTS ( SELECT id,ip,datevisite FROM TABLE3 WHERE visitemembre.id = TABLE1.idpub and ip='$ip' and datevisite=CURDATE()) ORDER BY gaineuros desc
Je dois y ajouter le lien avec la TABLE2 pour n'afficher que les lignes de TABLE1 pour lesquels la somme des correspondances dans TABLE2 sur la colonne VAL sont inférieurs au champ LIMITE de la TABLE1 (le lien entre TABLE1 et TABLE2 se fait par la champ idpub ensuite il y a un critère sur la date qui doit être la date du jour)
Par contre, que se passe-t-il si il n'y a aucune correspondance entre table1 et table2 le résultat de SUM retournera en principe la valeur NULL

10/12/2008, 11h28
pacmann
Salut !

Dans la requête qui t'a été donnée, tu as :
sum(COALESCE(val)).

Bon en fait, c'est :
Code:

1 2 sum(COALESCE(val, 0))
COALESCE(arg1, arg2, ..., argn) renvoie la première valeur non nulle parmi les args.

Donc s'il n'y a aucune correspondance, t'auras 0.
10/12/2008, 22h08
soazig

bonjour,

Citation:

Ce n'est pas :
Code :

WHERE TABLE2.DATE2=CURDATE() ???

Non car si tu mets ça dans le where lorsqu'il y n'aura aucune correspondance entre table1 et table2 , on ne prendra pas les données de la table2.
Quand tu fais une jointure externe, il faut bien mettre tous les filtres qui portent sur la table optionnel dans le critère de jointure et non dans le where.
C'est un piège classique.
A+
Soazig

Alors j'ai essayé avec vos remarques de faire ma requête mais elle ne semble pas correcte je vous la met ici tel quelle est :
Code:

1 2 3 4 5 6 7 8 9 SELECT p.idpub,p.type, p.quantiteutil, p.quantite, p.etat, p.adulte, p.gaineuros, p.lien, p.banniere, p.hauteur, p.largeur, p.limite FROM publicite p INNER JOIN detailgainmensuel d ON p.idpub=d.idpub and d.date=CURDATE() WHERE p.type='clicforce' AND p.quantiteutil<p.quantite AND p.etat='ACTIF' AND NOT EXISTS ( SELECT id,ip,datevisite FROM visitemembre WHERE visitemembre.id = p.idpub AND ip='1' AND datevisite=CURDATE()) HAVING sum(COALESCE(d.quantite,0))<p.limite ORDER BY p.gaineuros DESC
L'objectif de cette requete est de sélectionner toutes les publicités présentes dans le fichier publicité qui n'existent pas dans le fichier visitemembre avec les critères indiqués et pour lesquels la somme des quantités dans le fichier detailgainmensuel pour la date du jour et l'idpub est inférieur à la limite définie dans le fichier publicité
Si il n'y a pas de ligne correspondante dans detailgainmensuel celà n'a pas d'importance, les critères de date permettent juste de faire la somme juste sur la date du jour pour l'idpub concerné. Les publicités devront sortir impérativement qu'il y ait ou non correspondance sauf dans le cas ou la somme des quantités journalières est supérieur ou égale à la limite définie dans le fichier publicité
Ce code ne semble pas fonctionner et celà vient sans doute de HAVING et WHERE qui sont mal utilisés

Bonjour,
Dans ta requête il manque surtout le group by, dès que tu utilises un SUM, AVG, MIN, et il te faut un group by.
Donc avec le group by et correctement indenté, cela donne

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
SELECT   p.idpub       ,
         p.type        ,
         p.quantiteutil,
         p.quantite    ,
         p.etat        ,
         p.adulte      ,
         p.gaineuros   ,
         p.lien        ,
         p.banniere    ,
         p.hauteur     ,
         p.largeur     ,
         p.limite
FROM     publicite p
         INNER JOIN detailgainmensuel d
         ON       p.idpub=d.idpub
              AND d.date =CURDATE()
WHERE    p.type          ='clicforce'
     AND p.quantiteutil  <p.quantite
     AND p.etat          ='ACTIF'
     AND NOT EXISTS
         (SELECT id,
                ip ,
                datevisite
         FROM   visitemembre
         WHERE  visitemembre.id = p.idpub
            AND ip              ='1'
            AND datevisite      =CURDATE()
         )
group by  p.idpub       ,
         p.type        ,
         p.quantiteutil,
         p.quantite    ,
         p.etat        ,
         p.adulte      ,
         p.gaineuros   ,
         p.lien        ,
         p.banniere    ,
         p.hauteur     ,
         p.largeur     ,
         p.limite
HAVING   SUM(COALESCE(d.quantite,0))<p.limite
ORDER BY p.gaineuros DESC

Citation:

Ce code ne semble pas fonctionner et celà vient sans doute de HAVING et WHERE qui sont mal utilisés

Si tu as un message d'erreur, donne le nous, cela pourra nous aider.
Si tu veux les lignes qui n'ont pas de correspondance dans detailgainmensuel il faudra utiliser un left outer join et pas un inner join.
Pour en savoir plus http://sqlpro.developpez.com/cours/sqlaz/select/
A+
Soazig

Voici ma requête désormais :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
SELECT   p.idpub       ,
         p.type        ,
         p.quantiteutil,
         p.quantite    ,
         p.etat        ,
         p.adulte      ,
         p.gaineuros   ,
         p.lien        ,
         p.banniere    ,
         p.hauteur     ,
         p.largeur      ,
         p.limite
FROM     publicite p
         LEFT OUTER JOIN detailgainmensuel d
         ON       p.idpub=d.idpub
              AND d.date =CURDATE()
WHERE    p.type          ='clicforce'
     AND p.quantiteutil  <p.quantite
     AND p.etat          ='ACTIF'
     AND NOT EXISTS
         (SELECT id,
                ip ,
                datevisite
         FROM   visitemembre
         WHERE  visitemembre.id = p.idpub
            AND ip              ='1'
            AND datevisite      =CURDATE()
         )
GROUP BY  p.idpub       ,
         p.type        ,
         p.quantiteutil,
         p.quantite    ,
         p.etat        ,
         p.adulte      ,
         p.gaineuros   ,
         p.lien        ,
         p.banniere    ,
         p.hauteur     ,
         p.largeur      ,
         p.limite
HAVING   SUM(COALESCE(d.quantite,0))<p.limite
ORDER BY p.gaineuros DESC

Celà semble fonctionner je n'ai pas de messages d'erreurs je ferais des tests plus poussés
Sinon j'ai mis un LEFT OUTER JOIN car si il n'y a pas de ligne dans detailgainmensuel pour une publicité elle doit sortir impérativement mais bon après ma requête va se compliquer encore...
Je n'ai la nécessite de tester la limite que si la limite dans publicite est supérieur>0. Dans le cas où limite vaut 0 il n'y a pas de limite

12/12/2008, 03h50
pas30

Je ne sais pas si c'est moi mais je trouve cette requête très compliqué pour pas grand chose finalement. N'y a t il pas moyen d'ajouter une colonne dans le résultat qui contienne le résultat du sum()
Ex :
idpub : 1
hauteur : 60
largeur : 468
sommedetailgain : 50

Dans ce cas "sommedetailgain" contiendra la somme de d.quantite
Ce qui fera que j'aurais un jeu de résultat avec les publicités et le nombre de clic obtenus dans la journée par chacune
Ensuite il suffirait de tester cette colonne du jeu de résultat et donc inutile d'utiliser HAVING et de compliquer la requête de départ mais je ne sais pas si tout çà est possible
Merci de me donner votre avis

Pour moi,
Un SGBD digne de ce nom est fait pour ça, il y a des SGBD qui ne savent pas faire ce genre de choses mais ce n'est pas la majorité. Je ne sais pas dans quelle catégorie se place MYSQL donc je ne peux pas répondre à ta question.
En rajoutant le test sur la limite nulle on obtient

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
SELECT   p.idpub       ,
         p.type        ,
         p.quantiteutil,
         p.quantite    ,
         p.etat        ,
         p.adulte      ,
         p.gaineuros   ,
         p.lien        ,
         p.banniere    ,
         p.hauteur     ,
         p.largeur     ,
         p.limite,
	SUM(COALESCE(d.quantite,0)) as QTE
FROM     publicite p
         INNER JOIN detailgainmensuel d
         ON       p.idpub=d.idpub
              AND d.date =CURDATE()
WHERE    p.type          ='clicforce'
     AND p.quantiteutil  <p.quantite
     AND p.etat          ='ACTIF'
     AND NOT EXISTS
         (SELECT id,
                ip ,
                datevisite
         FROM   visitemembre
         WHERE  visitemembre.id = p.idpub
            AND ip              ='1'
            AND datevisite      =CURDATE()
         )
GROUP BY  p.idpub       ,
         p.type        ,
         p.quantiteutil,
         p.quantite    ,
         p.etat        ,
         p.adulte      ,
         p.gaineuros   ,
         p.lien        ,
         p.banniere    ,
         p.hauteur     ,
         p.largeur     ,
         p.limite
HAVING   (p.limite Is NULL OR (SUM(COALESCE(d.quantite,0))<p.limite))
ORDER BY p.gaineuros DESC

Personnellement je ne trouve pas cette requête particulièrement compliquée.
J'ai rajouté la somme de la quantité dans le résultat.
A+
Soazig