0

My situation is as follows. I have the query below and the query is doing its job pretty well. However I have excluded one able from it which is field_data_field_luogo and the rows inside this can have column entity_id and with different values in column field_luogo_tid , so what i want to do is , if entity_id is similar for 5 rows then merge the column of field_luogo_tid 's values in one column instead of having multiple rows.

SELECT a.nid,a.type,a.title,a.status,b.body_value,c.field_bici_value,
d.field_classifiche_url,d.field_classifiche_title,e.field_circuiti_tid,
f.field_corto_value,g.field_dcorto_value,h.field_medio_value,
i.field_dmedio_value,j.field_lungo_value,k.field_dlungo_value,
l.field_cronometraggio_tid,m.field_dalzeroit_value,n.field_data_value,
o.field_edizione_value,p.field_email_email,q.field_recapiti_value,
r.field_note_value,s.field_partenza_value,t.field_organizzatore_value,
u.field_star_value,v.field_superstar_value,w.field_tipomanifestazione_tid,
x.field_verifica_percorsi_value,tex.name as c_name , 
tex_t.name as cr_name,tex_n.name as m_name 
FROM node a 
LEFT JOIN field_data_body b ON b.entity_id = a.nid 
LEFT JOIN field_data_field_bici c ON c.entity_id = a.nid 
LEFT JOIN field_data_field_classifiche d ON d.entity_id = a.nid 
LEFT JOIN field_data_field_circuiti e ON e.entity_id = a.nid 
LEFT JOIN field_data_field_corto f ON f.entity_id = a.nid 
LEFT JOIN field_data_field_dcorto g ON g.entity_id = a.nid 
LEFT JOIN field_data_field_medio h ON h.entity_id = a.nid 
LEFT JOIN field_data_field_dmedio i ON i.entity_id = a.nid 
LEFT JOIN field_data_field_lungo j ON d.entity_id = a.nid 
LEFT JOIN field_data_field_dlungo k ON k.entity_id = a.nid 
LEFT JOIN field_data_field_cronometraggio l ON l.entity_id = a.nid 
LEFT JOIN field_data_field_dalzeroit m ON m.entity_id = a.nid 
LEFT JOIN field_data_field_data n ON n.entity_id = a.nid 
LEFT JOIN field_data_field_edizione o ON o.entity_id = a.nid 
LEFT JOIN field_data_field_email p ON p.entity_id = a.nid 
LEFT JOIN field_data_field_recapiti q ON q.entity_id = a.nid 
LEFT JOIN field_data_field_note r ON r.entity_id = a.nid 
LEFT JOIN field_data_field_partenza s ON s.entity_id = a.nid 
LEFT JOIN field_data_field_organizzatore t ON t.entity_id = a.nid 
LEFT JOIN field_data_field_star u ON u.entity_id = a.nid 
LEFT JOIN field_data_field_superstar v ON v.entity_id = a.nid 
LEFT JOIN field_data_field_tipomanifestazione w ON w.entity_id = a.nid 
LEFT JOIN field_data_field_verifica_percorsi x ON x.entity_id = a.nid 
LEFT JOIN taxonomy_term_data tex ON tex.tid = e.field_circuiti_tid 
LEFT JOIN taxonomy_term_data tex_t ON tex_t.tid = l.field_cronometraggio_tid LEFT JOIN taxonomy_term_data tex_n ON tex_n.tid = w.field_tipomanifestazione_tid 
WHERE a.type = 'manifestazione' 
AND a.status = 1  
GROUP BY a.nid

like if I have multiple rows with entity_id = 5 with field_luogo_id different in every row like :

2345
9877
9863
2374

by merging it will become :

2345,9877,9863,2374
  • Can you or someone ident this code to get a better view of the problem – Bill N. Varelli May 11 '15 at 12:32
  • Can you show the output you currently get and the desired output? – Colin 't Hart May 11 '15 at 12:37
  • With above query i get , all the rows , with right output , but as described in question i want to join also field_data_field_luogo and get it's column field_luogo_tid to be merged if there are more then 1 similar id's inside : enitity_id – Arsh Singh May 11 '15 at 12:44
  • @Colin'tHart see my quesiton 's edit – Arsh Singh May 11 '15 at 13:01
  • group_concat() should help you. See dev.mysql.com/doc/refman/5.0/en/… – Colin 't Hart May 11 '15 at 13:19
1

You can use group concat for this. example

mysql> select  * from dummy;
+----+-------+
| id | name  |
+----+-------+
|  1 | fiest |
|  1 | fiest |
|  1 | sec   |
+----+-------+
3 rows in set (0.00 sec)

mysql> select group_concat(id) from dummy group by id;
+------------------+
| group_concat(id) |
+------------------+
| 1,1,1            |
+------------------+
1 row in set (0.00 sec)
  • Works :D , thnx alot – Arsh Singh May 11 '15 at 14:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.