LEFT JOIN SUBQUERY

Asked

Viewed 7,164 times

0

Hello, I’m having a little doubt about SQL since I don’t understand anything about it. I have a table where I am making a select to see the updates with a subquery and do a LEFT JOIN here is the code:

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 it gives this result here:

SQL SELECT

The data I would like would be:

Projetos   25
Estágios   24
Divulgação 21

I believe I should use a GROUP BY somewhere, if I use DISTINCT at first it keeps duplicating the values only in place of NULL, 0.

Here is the table of posts_categories: inserir a descrição da imagem aqui

Postagens_categorias_view: inserir a descrição da imagem aqui

Postings:

inserir a descrição da imagem aqui

How do I do that part?

sql

  • already tried with distinct and group by in column updates?

  • I believe you have to do with INNER JOIN.

  • Detail your tables in question, will facilitate our understanding of the problem to give a possible answer. With the code you put in, I suppose it’s more interesting you take out the subselect and make a group by.

3 answers

1

If your select filter is right, the code can be this one:

SELECT T.ID,
       T.NOME,
       T.ATUALIZACOES
FROM (
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
GROUP BY A.ID,
         A.NOME) T
ORDER BY T.NOME DESC

1

From what I understand of your problem, the solution is this::

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

  • It was very close to what I wanted, the only problem is that this does not return anything when there is no update, in case I need to return 0, example : Projects 0, Stages 24 and Disclosure 0

  • This update information is on postagens_categorias_view or in postagens? In the table that this information is, you will use the left join. So it’s interesting that you put in your question the definition of tables, facilitates understanding.

  • Blz, I improved there for understanding by placing the other missing tables. The idea is when you have new post it "categorizes" which of the areas and separates each category with its updates.

  • I changed the answer. How can there be postagens_categorias unregistered postagens_categorias_view, nor in postagens it is necessary to be left Join on both joints so that the records on postagens_categorias appear. I changed the count(*), for count(c.*), I’m wondering if it’ll work, please take the test. I hope it works.

  • Unfortunately it didn’t work, it gave this message: #1064 - You have a syntax error in your SQL next to '*) the updates FROM postagens_categories as a LEFT JOIN postagens_cat' on line 1

  • Using left Join and Count(*) works? The answer you want appears?

  • More or less the answer appears, because it shows only the categories with updates and those without updates do not appear.

  • So, @Marcosschneider kept with a friend and he proposed this query that I put in the answer, please test there.

  • The explanation for the other solution not working is here https://stackoverflow.com/a/10949318/9916784 as I put it in the clause where fields that were part of the table that was joined with left join, when he came NULL he excluded the line to perform Count, so it did not appear.

Show 4 more comments

1

Try using Isnull (when null replaces zero) if this 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

Browser other questions tagged sql

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