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

[aieeeuuuuu]

Bonjour,

Cherchez avec le mot clef PIVOT, vous trouverez des exemple sur le forum.

Cela dit, ça semble être une mauvaise idée. Pourquoi ne pas faire cette transformation dans votre application ?

[xillibit]

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

J'ai refait ma requête avec un group by et en sélectionnant juste les colonnes que j'ai besoin :

Code :

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

SELECT c.name, c.description, c.address_1, c.address_2, c.address_city, c.address_state, c.address_zip, c.address_country, c.website, c.notes, c.phone, custcf.custom_field_id, custcf.value, cust.name 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 GROUP BY c.id, custcf.company_id, custcf.custom_field_id, custcf.value, custcf.modified, cust.id

Le résultat de cette requête me donne toujours le même nombre de 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
"name","description","address_1","address_2","address_city","address_state","address_zip","address_country","website","notes","phone","custom_field_id","value","name"
"Entreprise Toto"," ","907 Route des sapins"," ","DOUSSARD","\N","33880","\N","\N","\N","\N","1","00000000000023","siret"
"Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","33880","\N","\N","\N","\N","3","000000049","siren"
"Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","33880","\N","\N","\N","\N","4","8211Z","activite_principale"
"Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","33880","\N","\N","\N","\N","5","","forme_juridique"
"Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","33880","\N","\N","\N","\N","15","199304","annee_creation"
"Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","33880","\N","\N","\N","\N","17","NN","effectif_salarie"
"Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","33880","\N","\N","\N","\N","18","74104","code_comm"
"Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","33880","\N","\N","\N","\N","19","8211Z","code_ape"
"Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","33880","\N","\N","\N","\N","20","82","siege"
"Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","33880","\N","\N","\N","\N","22","Services administratifs combinés de bureau","lib_ape"
"Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","33880","\N","\N","\N","\N","23","","surface_commerce"
"Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","33880","\N","\N","\N","\N","24","3","nature_etab_entrepeneur_indi"
"Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","33880","\N","\N","\N","\N","27","","participation_part_prod_etab"
"Entreprise Toto"," ","907 Route des sapins"," ","Bordeaux","\N","33880","\N","\N","\N","\N","28","","categorie_entreprise"

Bonjour,

Cherchez avec le mot clef PIVOT, vous trouverez des exemple sur le forum.

Cela dit, ça semble être une mauvaise idée. Pourquoi ne pas faire cette transformation dans votre application ?

Bonjour,

Je vais cela de plus prés, cette requête je veux l'utiliser dans une application de SIG (esri arcgis 10.2 pour être précis)

[xillibit]

J'ai fait fait une requête imbriquée de la façon suivante:

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT *
FROM 
cob_companies AS table_alias,
(
    SELECT *
    FROM #__company_custom_cf AS cf
    INNER JOIN #__company_custom AS cust ON cust.id=cf.custom_field_id 
    WHERE company_id=table_alias.id
    GROUP BY cf.company_id, cf.custom_field_id, cf.value, cf.modified, cust.id
) AS entreprises

Cela m'affiche cette erreur :

Code :

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

1
2
3
ERREUR:  la sous-requête du FROM ne peut pas faire référence à d'autres relations
dans le même niveau de la requête
LINE 8:     WHERE company_id=table_alias.id

Je ne comprends pas comment faire marcher cette requête

[alassanediakite]

Salut
Il faut faire sortir le filtre...

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
SELECT table_alias.*,entreprises.*
FROM 
cob_companies AS table_alias,
(
    SELECT *
    FROM #__company_custom_cf AS cf
    INNER JOIN #__company_custom AS cust ON cust.id=cf.custom_field_id     
    --GROUP BY cf.company_id, cf.custom_field_id, cf.value, cf.modified, cust.id
) AS entreprises
WHERE entreprises.company_id=table_alias.id

Par ailleurs, le regroupement ne sert à rien s'il n'y a pas de fonction d’agrégation!
@+
PS/
J'avais pas vu le file de la discussion. Je propose juste une correction de la requête, pas une solution au problème.

[xillibit]

Cela ne groupe pas les résultats pour les entreprises qui ont le même id: