Repeated rows in MYSQL query

Question

Repeated rows in MYSQL query

Navigation

#1 by (2 votes)

2

Good I have a problem, it turns out that I have 3 tables that contain information about the event, another with the 'materials' (services) for that event and an intermediate table that generates me the N to N, when I want to show the event with all its materials I repeat the event I have something like this

MATERIAL EVENT1
MATERIAL EVENT2
etc

I can not think of how to solve it, I leave my code

CREATE DATABASE jumpingInflables
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;

CREATE TABLE Material (
    id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    nombre varchar(20)
);

CREATE TABLE Evento(
    id int NOT NULL AUTO_INCREMENT Primary Key,
    telefono varchar(20),
    cliente varchar(20),
    fecha date,
    horaInicio time,
    horaFin time,
    fechaRegistro date,
    cantChicos int,
    direccion varchar (32),
    observaciones varchar(32),
    costo decimal,
    duracion int
);


CREATE TABLE EventoMaterial(
    idEvento int not null,
    idMaterial int not null,
    PRIMARY KEY(idEvento,idMaterial),
    FOREIGN KEY (idEvento) REFERENCES Evento(id),
    FOREIGN KEY (idMaterial) REFERENCES Material(id)
);


INSERT INTO Material VALUES (null,'Cama Elastica chica'),
                                (null,'Cama Elastica 01'),
                            (null,'Cama Elastica 02'),
                            (null,'Cama Elastica 03'),
                            (null,'Castillo con Toro'),
                            (null,'Castillo con Rampa'),
                            (null,'Tejo 01'),
                            (null,'Tejo 02'),
                            (null,'Tejo 03'),
                            (null,'Futbolito 01'),
                            (null,'Futbolito 02'),
                            (null,'Pop'),
                            (null,'Algodon de Azucar'),
                            (null,'Musica');

INSERT INTO Evento VALUES (null,'Maria',CURDATE(),'12:30','14:30',CURDATE(),20,'Timoteo Aparicio 4169','Sin observaciones',1800,2),
                          (null,'Juan',CURDATE(),'16:30','19:30',CURDATE(),15,'Dir 2','En la calle',1600,2),
                          (null,'Lucas',CURDATE(),'21:30','23:30',CURDATE(),30,'Dir 3','En el patio',2400,3);

INSERT INTO EventoMaterial VALUES (1,1),
                                  (1,2),
                                  (2,1),
                                  (2,2);

My query:

"SELECT evento.*,material.nombre FROM evento,eventomaterial,material WHERE
    evento.id = eventomaterial.idEvento and idMaterial = material.id and evento.fecha = '$fecha'";

mysql sql php query

asked by Gaston 21.12.2017 в 04:56

source

1 answer

How to close an external application from my application how can I go back in git to a specific commit, without losing some data later?

score 2 · Accepted Answer

For me, the results you get seem normal to me. Then, from the application side you can interpret the data as you want.

If you really only want one row per event, the only alternative that occurs to me is that you want to concatenate all the materials together. This can be done by adding GROUP BY and GROUP_CONCAT :

SELECT evento.*, group_concat(material.nombre)
FROM evento,eventomaterial,material 
WHERE evento.id = eventomaterial.idEvento 
and idMaterial = material.id
and evento.fecha = curdate()
group by evento.id

I suggest, incidentally, favor the explicit joins. Also, in your case, it seems a good idea to use a LEFT JOIN to return the events without materials as well:

select e.*, group_concat(m.nombre)
  from evento e
  left join (
      select em.idEvento, m.nombre
        from eventomaterial em
        join material m
          on m.id = em.idMaterial) m
    on m.idEvento = e.id
 group by e.id

SQL Fiddle