LEFT JOIN SUBQUERY

Question

LEFT JOIN SUBQUERY

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

Hello, I have a small question about SQL since I do not understand anything about it. I have a table where I am doing a select to see the updates with a subquery and I make a LEFT JOIN where the code is:

SELECT a.id, a.nome,
(SELECT COUNT(c.id) FROM postagens as c WHERE c.data_cadastro > 
b.data_acesso AND c.id_categoria = a.id AND deletado = 0) as atualizacoes
FROM postagens_categorias as a
LEFT JOIN postagens_categorias_view as b ON a.id = b.id_categoria
WHERE a.deletado =0 AND a.id != 3 ORDER BY a.nome DESC

And give this result here:

ThedataI'dlikewouldbe:

Projetos25Estágios24Divulgação21

IthinkIshoulduseaGROUPBYsomewhere,ifIuseDISTINCTinthebeginningitwillstillduplicatevaluesonlyinplaceofNULL,0.

Hereisthepoststable_categories:

Postings_categories_view:

Posts:

How do I do this part?

sql

asked by anonymous 04.07.2018 / 16:33

2 answers

Accessing MainView from another class Sql Reporting Services: Two WebPortal URLs

score 0 · Answer 1

Try to use Isnull (when null replaces zero) if it does not work, put CASE

SELECT a.id, a.nome, isnull(count(*),0) atualizacoes
FROM postagens_categorias as a
    LEFT JOIN postagens_categorias_view as b ON a.id = b.id_categoria
    LEFT JOIN postagens as c on c.id_categoria = a.id
WHERE a.deletado = 0 AND
    c.deletado = 0 AND
    a.id != 3 AND 
    c.data_cadastro > b.data_acesso
GROUP BY a.id, a.nome
ORDER BY a.nome DESC

score 0 · Answer 2

From what I understood of your problem, the solution is as follows:

SELECT a.id, a.nome, count(d.id_categoria) as atualizacoes
FROM postagens_categorias as a
    LEFT JOIN (SELECT id_categoria from postagens_categorias_view b 
                INNER JOIN postagens c 
                    ON c.id_categoria = b.id_categoria 
                WHERE c.deletado = 0 AND 
                    c.data_cadastro > b.data_acesso
              ) d
        ON a.id = d.id_categoria
WHERE a.deletado = 0 AND
    a.id != 3
GROUP BY a.id, a.nome
ORDER BY a.nome DESC