Home / Questions / 20835
Accepted
NaCl
Asked: 2020-08-18 06:50:35 +0800 CST 2020-08-18 06:50:35 +0800 CST

Query does not return expected values

  • 772

Hi, I have the following query:

select 
    Seg.NombreUsuario, count(RelA.IDSeguidor) as `Siguiendo`, count(RelB.IDASeguir) as `Seguidores` 
from Perfiles as Seg 
    inner join Relaciones as RelA on RelA.IDSeguidor = Seg.ID 
    inner join Relaciones as RelB on RelB.IDASeguir = Seg.ID
where Seg.ID = 1
    Group by Seg.NombreUsuario
    Order by Seg.ID

For the following scheme:

create table Perfiles
(
    ID int primary key AUTO_INCREMENT,
    NombreUsuario varchar(25) not null,
    Clave varchar(25) not null,
    Biografia varchar(300) null,
    Edad int not null default 0,
    Seguidores int not null
);

create table Relaciones
(
    ID int primary key auto_increment,
    IDSeguidor int not null, 
    IDASeguir  int not null, 
    constraint fkIDSeg foreign key (IDSeguidor) references Perfiles(ID) 
    on update cascade on delete cascade, 
    constraint fkIDAs foreign key (IDASeguir) references Perfiles(ID) 
    on update cascade on delete cascade
);

When I run the query on a table that has the following values ​​(I've left the query so you can check with the same test data):

insert into 
    Perfiles(NombreUsuario, Clave, Biografia, Edad, Seguidores) 
values 
    ('root',      '123456',     'Una biografia 1',        18, 0), 
    ('prueba2',   'valor',      'Otra bio',               16, 0), 
    ('controles', 'contrase;a', 'Biografiaaaaa',          24, 0),
    ('cabello',   'perez34',    'Juanjoseguillermopere"', 30, 0),
    ('mrpelo',    'cebolla20',  'Mr Pelo1',               20, 0),
    ('cabeza1',   'lolololo',   'lololo cabezaaa',        13, 0),
    ('otrouser',  'user10',     'prueba 300',             17, 0),
    ('prueba3',   '123543',     'Prueba 3',               14, 0),
    ('xJeremy',   '13-SISN',    '-6-004 :))',             19, 0),
    ('elultimo',  'elultimo',   'el ultimo',              16, 0);

insert into
    Relaciones(IDSeguidor, IDAseguir)
Values
-- ID 1, sigue al ID 6 y asi...
    (1,6), (3,4), (1,8), (4,1), (5,1), (6,1),
    (5,2), (2,5), (8,1), (1,8), (6,1), (6,3),
    (2,6), (1,9), (9,1), (4,8), (3,7), (7,1),
    (7,3), (5,6), (5,7), (5,8), (8,5), (1,5),
    (9,8), (8,9), (10,1), (1,10), (10,2), (10,3),
    (10,4), (4,10), (8,10), (7,10), (3,10), (10,5),
    (5,10);

It gives me the following results:

+---------------+-----------+------------+
| NombreUsuario | Siguiendo | Seguidores |
+---------------+-----------+------------+
| root          | 48        | 48         |
+---------------+-----------+------------+

However, if I run the following query:

Select 
    Seg.NombreUsuario, Count(Rel.IDASeguir) as `Siguiendo`
from Relaciones as Rel
    inner join Perfiles as Seg on Rel.IDASeguir = Seg.ID
where Seg.ID = 1

It returns the correct amount to me in the field Siguiendo (6) .

What I can be doing wrong?

mysql

1 Answers

  1. Best Answer

    The problem is that you're counting once for each row of the dataset resulting from your joins, when you should be counting the distinct values ID​​of the relations table:

    select 
    Seg.NombreUsuario, 
    count(distinct RelA.ID) as `Siguiendo`, 
    count(distinct RelB.ID) as `Seguidores` 
from Perfiles as Seg 
    inner join Relaciones as RelA on RelA.IDSeguidor = Seg.ID 
    inner join Relaciones as RelB on RelB.IDASeguir = Seg.ID
where Seg.ID = 1
    Group by Seg.NombreUsuario
    Order by Seg.ID;

    This gives me the following results:

    ╔═══════════════╦═══════════╦════════════╗
║ NombreUsuario ║ Siguiendo ║ Seguidores ║
╠═══════════════╬═══════════╬════════════╣
║ root          ║         6 ║          8 ║
╚═══════════════╩═══════════╩════════════╝

    Here is a demo version of this query.

    • 6