recherche solution requête sql

**pas30** · 07/12/2008, 00h49

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 ?

**soazig** · 07/12/2008, 10h28

Bonjour,

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**pas30** · 10/12/2008, 00h31

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

AND TABLE2.DATE2=CURDATE()

Ce n'est pas :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

WHERE TABLE2.DATE2=CURDATE() ???

Voici ma requête de départ :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**pacmann** · 10/12/2008, 11h28

Salut !

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

Bon en fait, c'est :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

**soazig** · 10/12/2008, 22h08

bonjour,

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

**pas30** · 11/12/2008, 02h28

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 sql :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**soazig** · 11/12/2008, 07h30

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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

**pas30** · 11/12/2008, 16h55

Voici ma requête désormais :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**pas30** · 12/12/2008, 03h50

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

**soazig** · 12/12/2008, 07h08

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

recherche solution requête sql

Langage SQL

Discussions similaires

Partager

Partager