Discussion :

[xillibit]

Bonjour,

Je dois faire une requête avec une jointure pour sélectionner des éléments dans plusieurs tables mais la requête me sort vingt lignes, vu qu'un élément dans la table #__companies correspond à plusieurs dans la table #__company_custom_cf. Je voudrais grouper les lignes avec le même id ensemble :

Code :

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

SELECT * FROM #__companies AS c LEFT JOIN #__company_custom_cf AS custcf ON c.id=custcf.company_id LEFT JOIN #__company_custom AS cust ON cust.id=custcf.custom_field_id

Auriez-vous une idée ?

Cordialement

[Waldar]

Ajoutez ORDER BY c.id à la fin de votre requête.

[xillibit]

J'ai essayé en l'indication que vous m'avez donnée mais ce n'est bon :

Code :

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

SELECT * FROM #__companies AS c LEFT JOIN #__company_custom_cf AS custcf ON c.id=custcf.company_id LEFT JOIN #__company_custom AS cust ON cust.id=custcf.custom_field_id ORDER BY c.id

Je vais essayer de vous réexpliquer en vous mettant ici le résultat de la requête, pour chaque entreprise dans le résultat de ma requête j'ai 10 dix lignes comme cela il faudrait que toutes ces lignes soient combinées ensemble :

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
"id","owner_id","name","description","address_1","address_2","address_city","address_state","address_zip","address_country","website","created","notes","phone","modified","avatar","published","fax","email","twitter_user","facebook_url","flickr_url","youtube_url","company_id","custom_field_id","value","modified","id","name","values","type","required","multiple_selections","created","modified","ordering"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","5","","2014-12-10 15:13:39","5","forme_juridique","","text","0","0","2014-11-12 16:42:58","2014-11-12 16:42:58","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","3","310962949","2014-12-10 15:13:39","3","siren","","number","0","0","2014-11-12 16:41:31","2014-11-12 16:41:31","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","1","00000000000000","2014-12-10 15:13:39","1","siret","","number","0","0","2014-10-13 14:18:06","2014-10-13 14:18:06","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","15","199304","2014-12-10 15:13:39","15","annee_creation","","text","0","0","2014-11-12 16:56:54","2014-11-14 11:16:42","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","4","8211Z","2014-12-10 15:13:39","4","activite_principale","","text","0","0","2014-11-12 16:42:33","2014-11-12 16:42:33","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","28","","2014-12-10 15:13:39","28","categorie_entreprise","","text","0","0","2014-11-18 13:36:16","2014-11-18 13:36:16","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","17","NN","2014-12-10 15:13:39","17","effectif_salarie","","text","0","0","2014-11-12 16:57:42","2014-11-12 16:57:42","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","24","3","2014-12-10 15:13:39","24","nature_etab_entrepeneur_indi","","number","0","0","2014-11-18 11:57:07","2014-11-18 11:57:07","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","20","82","2014-12-10 15:13:39","20","siege","","number","0","0","2014-11-13 09:05:10","2014-11-13 09:05:10","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","18","74104","2014-12-10 15:13:39","18","code_comm","","number","0","0","2014-11-12 16:58:16","2014-11-12 16:58:16","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","27","","2014-12-10 15:13:39","27","participation_part_prod_etab","","number","0","0","2014-11-18 13:32:38","2014-11-18 13:32:38","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","19","8211Z","2014-12-10 15:13:39","19","code_ape","","text","0","0","2014-11-12 16:58:37","2014-11-13 09:41:00","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","22","Services administratifs combinés de bureau","2014-12-10 15:13:39","22","lib_ape","","text","0","0","2014-11-13 09:41:14","2014-11-13 09:41:14","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","23","","2014-12-10 15:13:39","23","surface_commerce","","number","0","0","2014-11-18 11:44:44","2014-11-18 11:44:44","\N"
"

Est-ce que c'est plus clair comme cela ?

[al1_24]

Je vais essayer de vous réexpliquer en vous mettant ici le résultat de la requête, pour chaque entreprise dans le résultat de ma requête j'ai 10 dix lignes comme cela il faudrait que toutes ces lignes soient combinées ensemble :
(...)
Est-ce que c'est plus clair comme cela ?

Ce n'est pas réellement plus clair.

• Que signifie "combiner ensemble" ?
• Quel est le résultat attendu ?
• Si l'une des lignes ou l'une des valeurs de colonne doit être privilégiée, quelle est la raison pour laquelle les autres lignes/valeurs ne sont pas retenues ?

[xillibit]

Actuellement quand je fais ma requête dans le résultat j'ai pour chaque entreprise toutes ces lignes:

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
"id","owner_id","name","description","address_1","address_2","address_city","address_state","address_zip","address_country","website","created","notes","phone","modified","avatar","published","fax","email","twitter_user","facebook_url","flickr_url","youtube_url","company_id","custom_field_id","value","modified","id","name","values","type","required","multiple_selections","created","modified","ordering"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","5","","2014-12-10 15:13:39","5","forme_juridique","","text","0","0","2014-11-12 16:42:58","2014-11-12 16:42:58","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","3","310962949","2014-12-10 15:13:39","3","siren","","number","0","0","2014-11-12 16:41:31","2014-11-12 16:41:31","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","1","00000000000000","2014-12-10 15:13:39","1","siret","","number","0","0","2014-10-13 14:18:06","2014-10-13 14:18:06","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","15","199304","2014-12-10 15:13:39","15","annee_creation","","text","0","0","2014-11-12 16:56:54","2014-11-14 11:16:42","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","4","8211Z","2014-12-10 15:13:39","4","activite_principale","","text","0","0","2014-11-12 16:42:33","2014-11-12 16:42:33","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","28","","2014-12-10 15:13:39","28","categorie_entreprise","","text","0","0","2014-11-18 13:36:16","2014-11-18 13:36:16","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","17","NN","2014-12-10 15:13:39","17","effectif_salarie","","text","0","0","2014-11-12 16:57:42","2014-11-12 16:57:42","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","24","3","2014-12-10 15:13:39","24","nature_etab_entrepeneur_indi","","number","0","0","2014-11-18 11:57:07","2014-11-18 11:57:07","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","20","82","2014-12-10 15:13:39","20","siege","","number","0","0","2014-11-13 09:05:10","2014-11-13 09:05:10","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","18","74104","2014-12-10 15:13:39","18","code_comm","","number","0","0","2014-11-12 16:58:16","2014-11-12 16:58:16","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","27","","2014-12-10 15:13:39","27","participation_part_prod_etab","","number","0","0","2014-11-18 13:32:38","2014-11-18 13:32:38","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","19","8211Z","2014-12-10 15:13:39","19","code_ape","","text","0","0","2014-11-12 16:58:37","2014-11-13 09:41:00","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","22","Services administratifs combinés de bureau","2014-12-10 15:13:39","22","lib_ape","","text","0","0","2014-11-13 09:41:14","2014-11-13 09:41:14","\N"
"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","\N","\N","2014-12-10 15:13:39","\N","\N","2014-12-10 15:13:39","\N","1","\N","\N","\N","\N","\N","\N","2966","23","","2014-12-10 15:13:39","23","surface_commerce","","number","0","0","2014-11-18 11:44:44","2014-11-18 11:44:44","\N"

Au lieu d'avoir toutes ces lignes pour une entreprise, il faudrait avoir les données dans la même ligne. Au lieu du résultat ci-dessus, le résultat de la requête deviendrait :

Code :

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

"2966","313","Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","74210","2966","5","","forme_juridique", "310962949","3","siren","00000000000000","1","siret", "199304","15","annee_creation","8211Z","4","activite_principale""28","","28","categorie_entreprise","NN","17","effectif_salarie","24","3","nature_etab_entrepeneur_indi", "","surface_commerce","27","","participation_part_prod_etab"

Dans la table #__companies on garde toutes les colonnes, dans la table #__company_custom on garde les colonnes :

id
name

Dans la table #__company_custom_cf on garde les colonnes :

company_id
custom_field_id
value

[al1_24]

Il faudrait déjà commencer par préciser dans la clause SELECT les colonnes à afficher et pas se contenter d'un SELECT *.

Un petit tour du côté des tutoriels (jointures, groupages) serait peut être utile