count with LEFT JOIN does not return when zero or null

Question

count with LEFT JOIN does not return when zero or null

Navigation

#1 by (0 votes)

3

I'm having a problem in a select where I need to get the data from 3 tables (search, users, and query_research). Where search_response can or does not have data. The current query is:

SELECT 
usuarios.nome AS nome_autor,
pc_pesquisa.*,
COUNT(pc_resposta_pesquisa.id) AS respostas 
FROM pc_pesquisa
    LEFT JOIN usuarios
        ON usuarios.id = pc_pesquisa.id_autor
    LEFT JOIN pc_resposta_pesquisa
        ON pc_resposta_pesquisa.id_pesquisa = pc_pesquisa.id
WHERE pc_pesquisa.id_tipo = 1 ORDER BY pc_pesquisa.data ASC

The data from the pc_earch and user tables return ok, but only returns the searches that have registered responses. It is necessary to return even if there is no (null or zero).

I currently have 2 searches registered, one with 4 replies and one with 0 replies. This query only returns the search that has answers.

mysql

asked by anonymous 18.12.2016 / 14:32

1 answer

How to save form data only if it comes from a particular site? Video autoplay problem

score 0 · Accepted Answer

I was able to solve the problem, apparently I needed to group the results. The query looks like this:

SELECT 
usuarios.nome AS nome_autor,
pc_pesquisa.*,
COUNT(pc_resposta_pesquisa.id) AS respostas 
FROM pc_pesquisa
    LEFT JOIN usuarios
        ON usuarios.id = pc_pesquisa.id_autor
    LEFT JOIN pc_resposta_pesquisa
        ON pc_resposta_pesquisa.id_pesquisa = pc_pesquisa.id
WHERE pc_pesquisa.id_tipo = 1 
    GROUP BY pc_pesquisa.id 
    ORDER BY pc_pesquisa.data ASC

This returns even when there are 0 replies. I do not know why, but it works haha. So you have to close this question since the problem has been resolved.