Iniciar sesión
Crear cuenta
Crear cuentaCrear cuenta
mysql> select * from emociones;
+------------+----------------+
| id_emocion | nombre_emocion |
+------------+----------------+
| 1 | Enojo |
| 2 | Disgusto |
| 3 | Miedo |
| 4 | Felicidad |
| 5 | Tristeza |
| 6 | Sorpresa |
| 7 | Me gusta |
| 8 | Share |
+------------+----------------+
8 rows in set (0.00 sec)
mysql> select * from votos;
+---------+-----------+------------+------------+-------+-------+-------+----------------+---------------+
| id_voto | id_imagen | id_usuario | id_emocion | otro1 | otro2 | otro3 | fecha_registro | hora_registro |
+---------+-----------+------------+------------+-------+-------+-------+----------------+---------------+
| 1 | 1 | 17 | 4 | temor | | | 2017-02-01 | 05:39:34 |
| 2 | 1 | 17 | 6 | temor | | | 2017-02-01 | 05:39:34 |
| 3 | 1 | 17 | 7 | temor | | | 2017-02-01 | 05:39:34 |
| 4 | 2 | 17 | 4 | | | | 2017-02-01 | 05:39:54 |
| 5 | 2 | 17 | 7 | | | | 2017-02-01 | 05:39:54 |
| 6 | 3 | 17 | 5 | | | | 2017-02-01 | 07:16:35 |
| 7 | 4 | 17 | 1 | | | | 2017-02-01 | 08:26:25 |
| 8 | 5 | 17 | 4 | | | | 2017-02-02 | 08:34:10 |
| 35 | 5 | 46 | 7 | | | | 2017-02-15 | 12:39:45 |
| 36 | 6 | 46 | 6 | | | | 2017-02-15 | 12:40:01 |
| 37 | 1 | 49 | 2 | salud | | | 2017-02-15 | 14:12:20 |
| 38 | 1 | 49 | 6 | salud | | | 2017-02-15 | 14:12:20 |
| 39 | 2 | 49 | 2 | | | | 2017-02-15 | 14:12:33 |
| 40 | 2 | 49 | 7 | | | | 2017-02-15 | 14:12:33 |
+---------+-----------+------------+------------+-------+-------+-------+----------------+---------------+
+--------------------+------------------------+
| correo | emociones |
+--------------------+------------------------+
| correo@usuario.com | Enojo, Miedo, Trizteza |
| correo2@correo.com | Me gusta, Sorpresa |
+--------------------+------------------------+
SELECT u.correo AS correo, i.id_imagen as 'Bacon ID', e.nombre_emocion
FROM votos v
INNER JOIN usuarios u on v.id_usuario = u.id_usuario
INNER JOIN imagenes i on v.id_imagen = i.id_imagen
INNER JOIN emociones e ON v.id_emocion = e.id_emocion
SELECT u.correo AS correo, v.id_imagen AS 'Bacon ID', e.nombre_emocion AS Emocion
FROM votos v
INNER JOIN usuarios u ON v.id_usuario = u.id_usuario
INNER JOIN emociones e ON v.id_emocion = e.id_emocion
mysql> SELECT * FROM tabla;
+---------+----------------+
| persona | nombre_emocion |
+---------+----------------+
| hugo | Miedo |
| hugo | Felicidad |
| hugo | Me gusta |
| paco | Share |
| paco | Sorpresa |
| paco | Tristeza |
| luis | Me gusta |
+---------+----------------+
7 rows in set (0.00 sec)
mysql> SELECT persona, GROUP_CONCAT(nombre_emocion)
-> FROM tabla
-> GROUP BY persona;
+---------+------------------------------+
| persona | GROUP_CONCAT(nombre_emocion) |
+---------+------------------------------+
| hugo | Miedo,Felicidad,Me gusta |
| luis | Me gusta |
| paco | Share,Sorpresa,Tristeza |
+---------+------------------------------+
3 rows in set (0.00 sec)
SELECT u.correo AS correo, v.id_imagen AS 'Bacon ID', GROUP_CONCAT(v.id_emocion SEPARATOR ',') as 'Emocion'
FROM votos v
INNER JOIN usuarios u ON v.id_usuario = u.id_usuario
INNER JOIN emociones e ON v.id_emocion = e.id_emocion
GROUP BY u.correo, v.id_imagen
