Discussion :

[morpheuss]

Bonjour,

Je souhaiterais créer une requête SQL afin de pouvoir afficher la liste des clients et en même temps afficher le nombre de factures de ce client.

J'ai 3 tables:

1. CLIENTS
----------
id
nomclient
adresse1_fact
....

2. PROJETS
----------
id
numprojet
id_client (Est la liaison vers le id de la table CLIENTS)
....

3. PROJETS_FACTURES
-----------------------
id
id_projet (Est la liaison vers le id de la table PROJETS)
designation_facture
montant
remarque_facture
date_encodage
date_facturation
date_paiement_client


Donc, je souhaite lister tous les clients de la base CLIENTS et à côté de chaque client indiquer le nombre de factures de ce client.

Je sais faire cela sans problème en listant par projet, mais j'ai des difficulté en partant de la liste des clients.

Voici ce que cela donne par PROJETS

Code php :

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
$requete = "SELECT p.id, p.numprojet, p.description, p.id_client, p.datecreation, p.actif, p.actif_ouvrier, p.rem_obligatoire, p.delai, c.nomclient, p.repertoire, p.date_envoi, p.a_facturer, p.cloturer,
				pp.nbpointagepourceprojet, ppo.nbpointageouvrierpourceprojet, ppf.nbfactureouverte, ppfi.nbfactureimpayee, ppff.nbfacture 
 
	FROM $table_projets as p
	LEFT JOIN $table_clients as c ON c.id = p.id_client
 
	LEFT JOIN 
		( SELECT id_projet, COUNT(*) as nbpointagepourceprojet
		FROM $table_pointage_projet
		GROUP BY id_projet
		) as pp ON pp.id_projet=p.id
 
	LEFT JOIN 
		( SELECT id_projet, COUNT(*) as nbpointageouvrierpourceprojet
		FROM $table_pointage_projet_ouvrier
		GROUP BY id_projet
		) as ppo ON ppo.id_projet=p.id
 
	LEFT JOIN 
		( SELECT id_projet, date_facturation, COUNT(*) as nbfactureouverte
		FROM $table_projets_factures
		WHERE date_facturation='0'
		GROUP BY id_projet
		) as ppf ON ppf.id_projet=p.id
 
	LEFT JOIN 
		( SELECT id_projet, COUNT(*) as nbfactureimpayee
		FROM $table_projets_factures
		WHERE date_paiement_client='0' and date_facturation<>'0'
		GROUP BY id_projet
		) as ppfi ON ppfi.id_projet=p.id
 
	LEFT JOIN 
		( SELECT id_projet, date_facturation, COUNT(*) as nbfacture
		FROM $table_projets_factures
		GROUP BY id_projet
		) as ppff ON ppff.id_projet=p.id
";

Maintenant, en partant de la base CLIENTS, j'ai essayé ceci, mais ça ne fonctionne pas:

Code php :

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
$requete = "SELECT c.id, nomclient, adresse1_fact, adresse2_fact, cp_fact, ville_fact, pays_fact, code_pays_fact, 
				adresse1_liv, adresse2_liv, cp_liv, ville_liv, pays_liv, code_pays_liv, phone, fax, mail, web, p.idprojet, p.nbfactureouverte
				FROM $table_clients as c
 
				LEFT JOIN
					( SELECT id as idprojet, id_client, ppf.nbfactureouverte as nbfactureouverte
					FROM $table_projets as p
						LEFT JOIN 
						( SELECT id_projet, date_facturation, COUNT(*) as nbfactureouverte
						FROM $table_projets_factures as ppf
						WHERE date_facturation='0'
						GROUP BY id_projet
						) as ppf ON ppf.id_projet=p.id
 
					) as p ON p.id_client=c.id
 
 
				GROUP BY id_client
";

Je suis débutant en requêtes SQL et je ne sais même pas si c'est possible de faire une jointure dans une autre jointure.

Pouvez-vous m'aider ?

Merci

Steph

[al1_24]

j'ai essayé ceci, mais ça ne fonctionne pas

Qu'est-ce qui ne fonctionne pas ?
Tu obtiens un message d'erreur ? Que dit-il ?
Le résultat est erroné ? Donne nous un exemple avec le résultat obtenu, le résultat attendu et les données correspondantes.

[escartefigue]

Bonsoir

Votre 1ère requete semble correcte syntaxiquement, si les résultats obtenus ne sont pas ceux attendus, voir la remarque de Al1_24 ci-dessus

Par contre la deuxième ne l'est pas : les critères de groupage de la requête principale et de la table dérivée "ppf" sont incohérents avec ceux de la clause SELECT
MySQL autorise cette entorse à la norme SQL, mais c'est le seul SGBD à le faire, et ça n'a aucun sens en termes de résultats

Attention : vous parlez de base client, mais il s'agit d'une table

EDIT : ah non, en relisant, je vois que dans la 1ère requête il y a les mêmes erreurs que dans la deuxième, GROUP BYincohérent avec SELECT, supprimez les colonnes inutiles pour corriger ça (ex date_facturation dans la table PPF)

[Artemus24]

Salut à tous.

Avec un jeu d'essai, il aurait été plus facile de construire vos deux requêtes.
Pour la première requête, deux remarques :

1) une date non renseignée est à null. Une date à zéro ne veut rien dire.
Quel est le type utilisé pour vos dates ?

2) pour le "groupe by", vous devez faire référence aux colonnes qui vont subir le critère de regroupement.
Donc tout ce qui n'est pas après un "group by" dans le select doit être référencé par une fonction de regroupement, comme un "count(*)" ou un "sum()".

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
SELECT        p.id,
              p.numprojet,
              p.description,
              p.id_client,
              p.datecreation,
              p.actif,
              p.actif_ouvrier,
              p.rem_obligatoire,
              p.delai,
              c.nomclient,
              p.repertoire,
              p.date_envoi,
              p.a_facturer,
              p.cloturer,
             pp.nbpointagepourceprojet,
            ppo.nbpointageouvrierpourceprojet,
            ppf.nbfactureouverte,
           ppfi.nbfactureimpayee,
           ppff.nbfacture
 
     FROM  $table_projets as p
 
LEFT JOIN  $table_clients as c
       ON  c.id = p.id_client
 
LEFT JOIN  (   SELECT  id_projet,
                       COUNT(*) as nbpointagepourceprojet
 
                 FROM  $table_pointage_projet
 
             GROUP BY  id_projet
           ) as pp
       ON  pp.id_projet = p.id
 
LEFT JOIN  (   SELECT  id_projet,
                       COUNT(*) as nbpointageouvrierpourceprojet
 
                 FROM  $table_pointage_projet_ouvrier
 
             GROUP BY  id_projet
           ) as ppo
       ON  ppo.id_projet = p.id
 
LEFT JOIN  (   SELECT  id_projet,
                       COUNT(*) as nbfactureouverte
 
                 FROM  $table_projets_factures
                WHERE  date_facturation is null
 
             GROUP BY  id_projet
           ) as ppf
       ON  ppf.id_projet = p.id
 
LEFT JOIN  (   SELECT  id_projet,
                       COUNT(*) as nbfactureimpayee
 
                 FROM  $table_projets_factures
                WHERE  date_paiement_client is null
                  and  date_facturation is not null
 
             GROUP BY  id_projet
           ) as ppfi
       ON  ppfi.id_projet = p.id
 
LEFT JOIN  (   SELECT  id_projet,
                       COUNT(*) as nbfacture
 
                 FROM  $table_projets_factures
 
             GROUP BY  id_projet
           ) as ppff
       ON  ppff.id_projet = p.id;

Pour la seconde requête, il st inutile de passer la table "$table_projets" par une sous-requête comme vous l'avez fait.
Utilisez les alias afin d'associer les colonnes aux tables.

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
SELECT c.id,
       c.nomclient,
       c.adresse1_fact,
       c.adresse2_fact,
       c.cp_fact,
       c.ville_fact,
       c.p ays_fact,
       c.code_pays_fact,
       c.adresse1_liv,
       c.adresse2_liv,
       c.cp_liv,
       c.ville_liv,
       c.pays_liv,
       c.code_pays_liv,
       c.phone,
       c.fax,
       c.mail,
       c.web,
 
       p.idprojet,
       p.nbfactureouverte
 
FROM $table_clients as c
 
LEFT JOIN  $table_projets as p
       ON  p.id_client = c.id
 
left JOIN  (   SELECT  id_projet,
                       COUNT(*) as nbfactureouverte
 
                 FROM  $table_projets_factures
                WHERE  date_facturation is null
 
             GROUP BY  id_projet
           ) as ppf
       ON  ppf.id_projet = p.id;

@+

[morpheuss]

Désolé pour mes réponses tardives.

Voici ci dessous un exemple de données dans ma table 'projets_factures'


les valeurs des dates ne sont jamais vide car j'ai placé une valeur par défaut '0000-00-00' c'est pour cela que je compare ma date = '0' (même si ce n'est probablement pas la meilleur solution ça fonctionne)



Je n'ai peut être pas été claire, désolé, voici ce que je souhaite avoir comme résultat:

Lister tous les clients par ordre alphabétique et à côté de chaque client voir le nombre de facture total de ce client, le nombre de facture sans date de facturation et le nombre de facture sans date de paiement client.

Mon soucis principal est que le id_client ne se trouve pas dans la table 'projets_factures' et que je souhaiterais réaliser cela dans la même requete sql.


Dans mon 1er message, la 1er requete fonctionne et me permet d'afficher la liste des projets et leurs nombres de facture.
Voici un des résultats de cette requete:


Je souhaiterais donc la même chose mais en partant de la table 'Clients'.

En espérant avoir été plus claire

Steph

[CinePhil]

Essayez un truc de ce genre :

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
SELECT c.id AS id_client, c.nomclient, 
	COUNT(*) AS nb_factures,
	SUM
	(
		CASE 
			WHEN pf.date-date_facturation = '0000-00-00' THEN 1 
			ELSE 0 
		END
	) AS nb_factures_sans_date_facturation,
	SUM
	(
		CASE WHEN pf.date_paiement_client = '0000-00-00' THEN 1 
			ELSE 0 
		END
	) AS nb_factures_sans_date_paiement_client
FROM client c
LEFT OUTER JOIN projet p ON p.id_client = c.id
	LEFT OUTER JOIN projet_facture pf ON pf.id_projet = p.id
GROUP BY c.id AS id_client, c.nomclient

Au passage...
1) Vos tables devraient être nommées au singulier.

2) À quoi sert d'utiliser des variables pour le nom des tables ?

[morpheuss]

"À quoi sert d'utiliser des variables pour le nom des tables ?"

J'avais fais cela au cas ou le nom de ma table changerait. Bon peut être pas très utile, soyez indulgent je débute ;-)

Si j'essaye le code ci-dessous:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT c.id, c.nomclient, 
	COUNT(*) AS nb_factures,
	SUM
	(
		CASE 
			WHEN pf.date_facturation = '0000-00-00' THEN 1 
			ELSE 0 
		END
	) AS nb_factures_sans_date_facturation
FROM $table_clients as c
LEFT OUTER JOIN $table_projets p ON p.id_client = c.id
LEFT OUTER JOIN $table_projets_factures pf ON pf.id_projet = p.id
GROUP BY c.id, c.nomclient
ORDER BY c.nomclient

J'ai comme erreur: Erreur lors de la requete: ('. 1630 .') '. FUNCTION pointage.SUM does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual); = 1630;

Si je supprime la SUM ça fonctionne bien que le nb_factures ne me donne pas le nombre de facture correct.

Code :

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

1
2
3
4
5
6
7
SELECT c.id, c.nomclient, 
	COUNT(*) AS nb_factures
FROM $table_clients as c
LEFT OUTER JOIN $table_projets p ON p.id_client = c.id
LEFT OUTER JOIN $table_projets_factures pf ON pf.id_projet = p.id
GROUP BY c.id, c.nomclient
ORDER BY c.nomclient

Avez-vous une idée pour l'erreur SUM ?

[escartefigue]

les valeurs des dates ne sont jamais vide car j'ai placé une valeur par défaut '0000-00-00' c'est pour cela que je compare ma date = '0' (même si ce n'est probablement pas la meilleur solution ça fonctionne)

Sauf que "0000-00-00" n'est pas une date valide, et c'est différent de zéro
La plus petite valeur valide pour une date c'est le 1er janvier de l'an 1, soit "0001-01-01"

Mon soucis principal est que le id_client ne se trouve pas dans la table 'projets_factures' et que je souhaiterais réaliser cela dans la même requete sql.

Soit la table "Projets_factures" (à renommer au singulier, et "facture" serait plus simple) est la table des factures, auquel cas le n° de client devrait y être en FK faisant référence à la table client
En ce cas, la date de facturation ne devrait jamais être à zéro, c'est réglementaire !

Soit c'est une table en relation avec la table des facture, auquel cas il devrait s'y trouver la FK identifiant la facture, mais pas le libellé de la facture ni la date de facture, ni tout autre attribut redondant avec ceux de la table "facture"

[morpheuss]

Ok ça fonctionne

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 c.id, c.nomclient, 
	SUM(
		CASE 
			WHEN pf.date_encodage <> '' THEN 1 
			ELSE 0 
		END
	) AS nb_factures,
	SUM(
		CASE 
			WHEN pf.date_facturation = '0000-00-00' THEN 1 
			ELSE 0 
		END
	) AS nb_factures_sans_date_facturation,
	SUM(
		CASE 
			WHEN pf.date_paiement_client = '0000-00-00' THEN 1 
			ELSE 0 
		END
	) AS nb_factures_sans_date_paiement_client
FROM $table_clients as c
LEFT OUTER JOIN $table_projets p ON p.id_client = c.id
LEFT OUTER JOIN $table_projets_factures as pf ON pf.id_projet = p.id
GROUP BY c.id, c.nomclient
ORDER BY c.nomclient

Merci beaucoup pour votre aide, le résultat est correct ;-)

Bonne journée.

Steph