Requête SQL LEFT OUTER JOIN

**Az2000** · 18/02/2009, 13h54

Pour que la date soit prise en compte dans les prix, il faut lier l'ensemble report_prix et report_tete en INNER, et lier le tout en LEFT OUTER à produit.

La requête devient celle-là :

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
 
SELECT pc.id_produit, pt.type, pc.id_type, pc.produit, AVG(rp.prix) AS prix_moyen, MIN(rp.prix) AS prix_mini, MAX(rp.prix) AS prix_maxi
FROM produit_conc pc
JOIN produit_type pt
ON pt.id_type=pc.id_type
 
LEFT JOIN report_prix rp
JOIN report_tete rt
ON rt.id_report=rp.id_report
AND YEAR(rt.date_visite)='".$annee."'
AND MONTH(rt.date_visite)='".$id_mois."'
 
ON rp.id_produit=pc.id_produit
GROUP BY pc.id_produit
ORDER BY pt.type DESC, pc.produit

Ca devrait marcher.

**dam28800** · 18/02/2009, 14h48

Bonjour,

Je viens de tster ta requête et à priori elle ne fonctionne pas (le critère date n'est pas pris en compte car date à NULL)

Merci de ton aide.

**Az2000** · 18/02/2009, 19h01

Envoyé par dam28800

En effet si j'affiche rt.date_visite (en l'ajoutant dans ma requête au niveau du select) et que j'execute ma requete dans MySQL j'ai NULL qui apparait...

Quand tu parles de date à NULL, c'est quand tu fais ça ?

Tu n'as pas le droit de le faire car tu as plusieurs dates possible pour un produit.
Soit tu mets un max et/ou un min de date_visite dans ton select.
Soit tu rajoutes date_visite dans ton group by, mais dans ce cas, tu auras plus de lignes.

**Waldar** · 18/02/2009, 20h05

Ce serait bien de commencer à nous montrer quelques lignes de vos tables pour lesquelles la requête pose problème.

**dam28800** · 19/02/2009, 08h38

Voici mes tables :

Report tête :

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
CREATE TABLE `report_tete` (
  `id_report` smallint(5) unsigned NOT NULL auto_increment,
  `id_util` tinyint(3) unsigned NOT NULL default '0',
  `id_pdv` smallint(5) unsigned NOT NULL default '0',
  `date_visite` date NOT NULL default '0000-00-00',
  `dure_visite` tinyint(3) unsigned NOT NULL default '0',
  `semaine` tinyint(3) unsigned NOT NULL default '0',
  `date_saisie` date NOT NULL default '0000-00-00',
  `heure_saisie` time NOT NULL default '00:00:00',
  `commentaire` text NOT NULL,
  `etat` char(1) NOT NULL default '',
  PRIMARY KEY  (`id_report`),
  KEY `id_util` (`id_util`),
  KEY `id_mag` (`id_pdv`),
  KEY `etat` (`etat`),
  KEY `date_visite` (`date_visite`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

Produit_conc :

Code :

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

1
2
3
4
5
6
7
 
CREATE TABLE `produit_conc` (
  `id_produit` smallint(5) unsigned NOT NULL auto_increment,
  `id_marque` char(2) NOT NULL default '',
  `produit` varchar(25) NOT NULL default '',
  PRIMARY KEY  (`id_produit`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=27 ;

Report_prix :

Code :

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

1
2
3
4
5
6
7
8
9
 
CREATE TABLE `report_prix` (
  `id_rp` int(10) unsigned NOT NULL auto_increment,
  `id_report` int(10) unsigned NOT NULL default '0',
  `id_produit` int(10) unsigned NOT NULL default '0',
  `prix` decimal(5,2) NOT NULL default '0.00',
  `prix_achat` decimal(5,2) NOT NULL default '0.00',
  PRIMARY KEY  (`id_rp`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;

Le principe est de saisir une visite pour un PDV (point de vente) qui créer une entête de visite dans (report_tete) et plusieurs lignes de releve de prix (dans releve de prix) je souhaite ensuite savoir pour tout les produits le prix min, max, et moyen pour un mois donné par exemple.

Voilà si ça peut vous aider, merci d'avance.

**Waldar** · 19/02/2009, 11h05

Ajoutez également deux trois insertions parmis les résultats qui vous posent problème, comme ça on pourra reproduire chez nous vos requêtes.

**dam28800** · 20/02/2009, 08h46

Ok, voilà des données de deux visites une effectué en janvier et l'autre en février, donc :

Deux entrées dans ma table report_tete :

Code :

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

1
2
3
 
INSERT INTO `report_tete` VALUES (1, 1, 2, '2009-02-02', 0, 6, '2009-02-20', '08:40:59', '', 'C');
INSERT INTO `report_tete` VALUES (2, 1, 2, '2009-01-20', 0, 4, '2009-02-20', '08:41:27', '', 'D');

Ma table de produit avec 4 produits :

Code :

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

1
2
3
4
5
 
INSERT INTO `produit_conc` VALUES (1, '1', 'PRODUIT1');
INSERT INTO `produit_conc` VALUES (2, '1', 'PRODUIT2');
INSERT INTO `produit_conc` VALUES (3, '1', 'PRODUIT3');
INSERT INTO `produit_conc` VALUES (4, '1', 'PRODUIT4');

Et ma table qui enregistre les prix des produits à chaque visite :

Code :

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

1
2
3
4
5
6
7
8
 
 
INSERT INTO `report_prix` VALUES (1, 1, 1, 102.00, 0.00);
INSERT INTO `report_prix` VALUES (2, 1, 2, 202.00, 0.00);
INSERT INTO `report_prix` VALUES (3, 1, 3, 302.00, 0.00);
INSERT INTO `report_prix` VALUES (4, 2, 1, 101.00, 0.00);
INSERT INTO `report_prix` VALUES (5, 2, 2, 201.00, 0.00);
INSERT INTO `report_prix` VALUES (6, 2, 3, 301.00, 0.00);

Les prix de janvier finissent par un 1 et de février par un 2 et ils sont liés avec report tete et donc date de visite.

Merci d'avance de ton aide.

**Waldar** · 20/02/2009, 11h16

Ok, avec vos données (requête faite sous Oracle 11g, j'ai du changer les YEAR et MONTH par extract).
La requête suivante est celle proposée par Az2000 :

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
SELECT
    pc.id_produit as p_id,
    pc.id_marque as m_id,
    pc.produit,
    AVG(rp.prix) AS avg,
    MIN(rp.prix) AS min,
    MAX(rp.prix) AS max
FROM
    produit_conc pc
    LEFT OUTER JOIN report_prix rp
    INNER JOIN report_tete rt
      ON rt.id_report = rp.id_report
     AND extract(YEAR from rt.date_visite) = 2009
     AND extract(MONTH from rt.date_visite) = 1
      ON rp.id_produit = pc.id_produit
GROUP BY
    pc.id_produit,
    pc.id_marque,
    pc.produit
ORDER BY
    pc.id_produit asc,
    pc.produit asc

Code :

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

1
2
3
4
5
P_ID	M_ID	PRODUIT		AVG	MIN	MAX
1	1	PRODUIT1	101	101	101
2	1	PRODUIT2	201	201	201
3	1	PRODUIT3	301	301	301
4	1	PRODUIT4

Avec le mois à 2 :

Code :

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

1
2
3
4
5
P_ID	M_ID	PRODUIT		AVG	MIN	MAX
1	1	PRODUIT1	102	102	102
2	1	PRODUIT2	202	202	202
3	1	PRODUIT3	302	302	302
4	1	PRODUIT4

Qu'est-ce qui ne vous convient pas dans ces résultats ?

J'obtiens les mêmes résultats avec la requête suivante :

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
SELECT
    pc.id_produit as p_id,
    pc.id_marque as m_id,
    pc.produit,
    AVG(rp.prix) AS avg,
    MIN(rp.prix) AS min,
    MAX(rp.prix) AS max
FROM
    produit_conc pc
    LEFT OUTER JOIN report_prix rp
      ON rp.id_produit = pc.id_produit
    LEFT OUTER JOIN report_tete rt
      ON rt.id_report = rp.id_report
WHERE
   (extract(YEAR from rt.date_visite) = 2009
AND extract(MONTH from rt.date_visite) = 1)
 OR rt.date_visite is null     
GROUP BY
    pc.id_produit,
    pc.id_marque,
    pc.produit
ORDER BY
    pc.id_produit asc,
    pc.produit asc

**dam28800** · 20/02/2009, 11h40

Bonjour,

Alors je viens de tester les deux requêtes :

La première : critère date ne fonctionne pas et il me met tous les produits au même prix
La deuxième : tout fonctionne nikel !

Merci à toi et à tout les autres pour votre aide !

@ Bientôt !

**dam28800** · 20/02/2009, 11h58

Eh non, désolé...

Je viens de tester la requête qui fonctionnait :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT pc.id_produit, pc.id_type, pc.produit, AVG( rp.prix ) AS prix_moyen, MIN( rp.prix ) AS prix_mini, MAX( rp.prix ) AS prix_maxi
FROM produit_con pc
LEFT OUTER JOIN report_prix rp ON rp.id_produit = pc.id_produit
LEFT OUTER JOIN report_tete rt ON rt.id_report = rp.id_report
WHERE YEAR( rt.date_visite ) = '2009'
AND MONTH( rt.date_visite ) = '03'
OR rt.date_visite IS NULL 
GROUP BY pc.id_produit, pc.id_type, pc.produit
ORDER BY pc.id_produit ASC , pc.produit ASC

En fait quand j'ai pas de données, par exemple pouir le mois de mars, je veux qu'il m'affiche tous les produits à 0.

Il m'affiche que les produits ou pc.id_type=2, ce sont des produits qui n'ont jamais été relevé...

Mais ce qui ont déjà été relevé en janvier ou février ne sont pas affiché...

Une idée ? Merci d'avance.

**Waldar** · 20/02/2009, 12h51

Oui, si vous enlevez des parenthèses nécessaires parce que vous trouvez ça moche, vous ne pouvez vous en prendre qu'à vous !

**Az2000** · 20/02/2009, 13h31

Envoyé par Waldar

J'obtiens les mêmes résultats avec la requête suivante :

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
SELECT
    pc.id_produit as p_id,
    pc.id_marque as m_id,
    pc.produit,
    AVG(rp.prix) AS avg,
    MIN(rp.prix) AS min,
    MAX(rp.prix) AS max
FROM
    produit_conc pc
    LEFT OUTER JOIN report_prix rp
      ON rp.id_produit = pc.id_produit
    LEFT OUTER JOIN report_tete rt
      ON rt.id_report = rp.id_report
WHERE
   (extract(YEAR from rt.date_visite) = 2009
AND extract(MONTH from rt.date_visite) = 1)
 OR rt.date_visite is null     
GROUP BY
    pc.id_produit,
    pc.id_marque,
    pc.produit
ORDER BY
    pc.id_produit asc,
    pc.produit asc

Attention, votre requête ne fonctionne que parce que "produit 4" n'a jamais de données, si pour le mois de mars, vous mettez des prix à tous les produits, la sélection au mois de février ne présentera plus le "produit 4", car sa date_visite ne sera plus à null.

**Waldar** · 20/02/2009, 15h00

Tout-à-fait d'accord Az2000, et vous avez raison de le surligner car de prime abord celà m'avait échappé.

MySQL apparement digère mal la première syntaxe qui est correcte.
dam28800, essayez en rajoutant d'autres parenthèse dans le FROM :

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
SELECT
    pc.id_produit AS p_id,
    pc.id_marque AS m_id,
    pc.produit,
    AVG(rp.prix) AS avg,
    MIN(rp.prix) AS min,
    MAX(rp.prix) AS max
FROM
    produit_conc pc
    LEFT OUTER JOIN (
      report_prix rp
      INNER JOIN report_tete rt
        ON rt.id_report = rp.id_report
       AND extract(YEAR FROM rt.date_visite) = 2009
       AND extract(MONTH FROM rt.date_visite) = 3) rp ON rp.id_produit = pc.id_produit
GROUP BY
    pc.id_produit,
    pc.id_marque,
    pc.produit
ORDER BY
    pc.id_produit ASC,
    pc.produit ASC

Code :

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

1
2
3
4
5
P_ID	M_ID	PRODUIT	AVG	MIN	MAX
1	1	PRODUIT1			
2	1	PRODUIT2			
3	1	PRODUIT3			
4	1	PRODUIT4

**rsc** · 23/02/2009, 08h54

Code :

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

1
2
LEFT OUTER JOIN report_tete rt ON rt.id_report = rp.id_report
AND rt.date_visite LIKE '2009-01%'

Il n'y a pas un problème là ? On fait une jointure externe, sur laquelle on essaie d'appliquer un filtre. Ca n'est pas contradictoire ?

**al1_24** · 23/02/2009, 08h57

Envoyé par rsc

Il n'y a pas un problème là ? On fait une jointure externe, sur laquelle on essaie d'appliquer un filtre. Ca n'est pas contradictoire ?

Aucun problème.
Le filtre s'applique avant la jointure et c'est exactement le but recherché.

**dam28800** · 24/02/2009, 09h48

Bonjour,

Je viens donc d'essayer la dernière solution :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT pc.id_produit, pc.id_type, pc.produit, AVG( rp.prix ) AS prix_moyen, MIN( rp.prix ) AS prix_mini, MAX( rp.prix ) AS prix_maxi
FROM produit_conc pc
LEFT OUTER JOIN (
report_prix rp
INNER JOIN report_tete rt ON rt.id_report = rp.id_report
AND extract( YEAR
FROM rt.date_visite ) =2009
AND extract( 
MONTH FROM rt.date_visite ) =2
) ON rp.id_produit = pc.id_produit
GROUP BY pc.id_produit, pc.id_type, pc.produit
ORDER BY pc.id_produit ASC , pc.produit ASC 
LIMIT 0 , 30

Le problème est que mes critères dates ne sont plus pris en compte...(même problème qu'aun début)

Merci d'avance.

**Waldar** · 24/02/2009, 16h13

Je suis désolé mais je n'ai pas de MySQL sous la main.

La solution que je vous ai donné fonctionne pourtant bien avec Oracle, je pourrais tester avec SQL Server mais je ne pense pas que ça fasse avancer le schmilblik.

**dam28800** · 24/02/2009, 16h54

Ok,

Bon je vais reprende ma requête qui fonctionnait .

Merci à tous de votre aide c'est sympas !

**dam28800** · 27/02/2009, 10h31

Et oui c'est encore moi....

J'ai un nouveau problème avec cette même requête.
En effet j'applique cette requête :

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
 
SELECT pc.id_produit, pt.type, pc.produit, pt.id_type, (
 
SELECT AVG( prix ) 
FROM report_prix rp
JOIN report_tete rt ON rp.id_report = rt.id_report
AND YEAR( rt.date_visite ) = '2009'
AND MONTH( rt.date_visite ) = '02'
JOIN produit_conc ON rp.id_produit = produit_conc.id_produit
AND produit_conc.id_produit = pc.id_produit
) AS prix_moyen, (
 
SELECT MIN( prix ) 
FROM report_prix rp
JOIN report_tete rt ON rp.id_report = rt.id_report
AND YEAR( rt.date_visite ) = '2009'
AND MONTH( rt.date_visite ) = '02'
JOIN produit_conc ON rp.id_produit = produit_conc.id_produit
AND produit_conc.id_produit = pc.id_produit
) AS prix_mini, (
 
SELECT MAX( prix ) 
FROM report_prix rp
JOIN report_tete rt ON rp.id_report = rt.id_report
AND YEAR( rt.date_visite ) = '".$annee."'
AND MONTH( rt.date_visite ) = '02'
JOIN produit_conc ON rp.id_produit = produit_conc.id_produit
AND produit_conc.id_produit = pc.id_produit
) AS prix_maxi
FROM produit_conc pc
JOIN produit_type pt ON pt.id_type = pc.id_type
GROUP BY pc.id_produit, pc.id_type
ORDER BY pt.id_type ASC , pc.id_produit ASC

Moreque je l'execute avec MySQL 4.1.9 pas de problème.
En revanche avec MySQL 5.0.27 j'ai un jolie message :

Code :

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

#1054 - Champ 'pc.id_produit' inconnu dans on clause

Je n'ai bientôt plus de cheveux, rrrr !!

Merci d'avance de votre aide.

**CinePhil** · 27/02/2009, 13h21

Je viens de charger le format de vos tables et votre jeu de données fournis plus haut mais il manque la table produit_type.

Vous nous la donnez avec un nouveau jeu de données pour qu'on puisse enfin sortir de ce problème ?

Profitez-en pour nous redire exactement votre besoin.
D'après la requête, vous souhaitez afficher, par produit et par type, la moyenne, le prix mini et le prix maxi.
Je ne vois plus de LEFT JOIN qui a été mentionné plus haut donc ne seront pris en compte que les produits ayant une date de visite en février 2009.

Si c'est bien ce que vous voulez, il y a plus simple à mon avis mais je voudrais tester d'abord.

Requête SQL LEFT OUTER JOIN

Langage SQL

Discussions similaires

Partager

Partager