Display prayed results

Asked

Viewed 15 times

0

Good evening I’m creating a referral system, But the query result does not return users with zero indications

Here is my select

  SELECT 
   c.id, 
   c.nome, 
   funcao, 
   area, 
   count(p.cpf_cli) AS quantidade 
FROM col c 
LEFT JOIN indicacao p 
   ON p.id_colaborador = c.id 
WHERE 
   indicacao.data_ind BETWEEN `2018/09/20` AND '2018/09/21'
GROUP BY 
   c.id, c.nome 
ORDER BY
   c.nome
select join count

  • which database, mysql?

  • in fact, the function and area columns should be in the group by, otherwise it will error in your select

  • The database is Mysql, is working, but the collaborators who will not indicate does not return in the query

  • Example: Anderson 0 indications, maria 3 indications

  • without seeing the table data is difficult to say what it is, if putting an example of the data becomes easier to help

  • I can’t send photos,

  • CREATE TABLE col ( id int(10) NOT NULL, nome varchar(90) NOT NULL, funcao varchar(90) NOT NULL, area varchar(90) NOT NULL, PRIMARY KEY (id) CREATE TABLE IF NOT EXISTS indicacao ( nome_cli varchar(120) NOT NULL, cpf_cli varchar(14) NOT NULL, id_colaborador int(10) NOT NULL, data_ind datetime DEFAULT CURRENT_TIMESTAMP, status int(2) NOT NULL DEFAULT '0', PRIMARY KEY (cpf_cli), KEY id_colaborador (id_colaborador)

  • these are my related tables col(contributor) and indication

Show 3 more comments

1 answer

0

Hello, good night. Do it this way that will work, abçs:

SELECT c.id, c.name, function, area, Count(p.cpf_cli) Quantity AS FROM col c LEFT JOIN indication p ON p.id_collaborator = c.id WHERE date_format(indicator.data_ind, "%Y%m%d") BETWEEN '20180920' AND '20180921' GROUP BY c.id, c.name ORDER BY c.name

Browser other questions tagged select join count

You are not signed in. Login or sign up in order to post.