Home / Questions / 523443
Accepted
John Doe
Asked: 2022-08-24 10:02:02 +0800 CST 2022-08-24 10:02:02 +0800 CST

I get repeated values ​​when doing a query

  • 772

I have this categories table:

CREATE TABLE IF NOT EXISTS `categoria` (
`idcategoria` int(5) NOT NULL AUTO_INCREMENT,
`nombrecategoria` varchar(50) NOT NULL,
PRIMARY KEY (`idcategoria`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

And this expenses:

CREATE TABLE IF NOT EXISTS `gastos` (
`idgasto` int(5) NOT NULL AUTO_INCREMENT,
`cantidadgasto` int(50) NOT NULL,
`fecha` date NOT NULL,
`idcategoria` int(5) NOT NULL,
PRIMARY KEY (`idgasto`),
 CONSTRAINT fk_categoria
    FOREIGN KEY (idcategoria) 
        REFERENCES categoria(idcategoria)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Data:

    insert into categoria (idcategoria,nombrecategoria) values (5, "gasolina");
    insert into categoria (idcategoria,nombrecategoria) values (15, "comida");
    insert into categoria (idcategoria,nombrecategoria) values (25, "ropa");
    insert into categoria (idcategoria,nombrecategoria) values (35, "gimnasio");
    insert into gastos (idgasto,cantidadgasto,fecha,idcategoria) values (100, 25,"2022-03-22",5);
    insert into gastos (idgasto,cantidadgasto,fecha,idcategoria) values (105, 30,"2022-03-23",15);
    insert into gastos (idgasto,cantidadgasto,fecha,idcategoria) values (110, 40,"2022-03-25",25);

Using this select:

SELECT distinct g.cantidadgasto, c.nombrecategoria, g.fecha 
from gastos g, categoria c 
inner join gastos on gastos.idcategoria = c.idcategoria;

And I can't get it to show it to me without repeating, I don't know if the expenses table is wrong or what is wrong is the select, the case is that I want it to show me like this:

Amount Expense || Category Name || Date

mysql

2 Answers

  1. Best Answer

    Try this:

    • Don't use thedistinct
    • Do not make from2 tables, it is not necessary if you already apply theinner join
    select g.cantidadgasto, c.nombrecategoria, g.fecha 
from gastos g
inner join categoria c on c.idcategoria = g.idcategoria
    • 1

  2. You don't need to use distinct

    and the query, well, I do it like this:

    Select gastos.idgasto,gastos.cantidadgasto, gastos.fecha,gastos.idcategoria
From gastos 
INNER JOIN categoria ON categoria.idcategoria=gastos.idcategoria
group by gastos.idgasto // en caso que lo requieras

    • 1