Query does not return the expected values

Question

Query does not return the expected values

Navigation

#1 by (6 votes)

4

Good morning, 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 execute the query in a table that has the following values (I have left the query so they 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);

I get 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

I get the correct amount in the field Siguiendo (6) .

What can I be doing wrong?

mysql sql

asked by NaCl 17.08.2016 в 16:50

source

1 answer

How to give effect of flipping letter with css3 Share web content in Google+

score 6 · Accepted Answer

The problem is that you are counting once for each row of the data set resulting from your join s, when you should count the values other than ID of the relationships 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.