Query to show user data and show quantity

Question

Query to show user data and show quantity

Navigation

#1 by (2 votes)

0

Hi, I am currently doing a query but I think that the number of working hours has blocked me.

I try to show the id of a joint user with his first and last name, and to all this he shows how many prospects (clients) he has assigned.

My user table has the fields cedula, names, surnames My prospect table has the user field (which would be the user's ID)

I want to show is

userid, username, surname, number of prospects assigned.

So far what I have as a notion is the following

Select idusuario, count(*) from prospecto
where estado = 1
and usuario is not null
group by idusuario

But this refers to a table, now I want to show is two joint tables and I have something like that but I've come this far.

select p.IDUSUARIO, u.NOMBRES, u.APELLIDOS, (select count(*) from prospecto where idusuario ) as cantidad 
from PROSPECTO p, USUARIO u
where p.estado = 1
group by p.IDUSUARIO, u.nombres, u.APELLIDOS

Any suggestions?

sql-server

asked by Jorge 15.09.2016 в 17:22

source

1 answer

I lost the Session variables in asp.net MVC DNS server, return 127.0.0.1 when the computer that makes the query has the IP of the answer

score 2 · Accepted Answer

A simple LEFT JOIN will give you the results:

SELECT  u.IdUsuario,
        u.Nombres,
        u.Apellidos,
        ISNULL(p.Cantidad,0) Cantidad
FROM dbo.USUARIO u
LEFT JOIN ( SELECT IdUsuario, COUNT(*) Cantidad
            FROM dbo.PROSPECTO
            WHERE estado = 1
            AND usuario IS NOT NULL
            GROUP BY IdUsuario) p
    ON u.IdUsuario = p.IdUsuario;