Query with conditions in SQL SERVER

Question

Follow the tables below with my question

I have a problem, where I make a filter in which I need to get the posts that have the column post_parent of the post table with value other than 0, and also need to check if the id_author of the post is equal to the id_author of the post that comes in the column post_parent. It is in this second item that I am getting hammered. I built my query, but I don’t know if it is 100%. Below:

SELECT p.id_postagem, p.titulo, a.nome
FROM postagem p JOIN autor a
ON p.id_autor = a.id_autor
WHERE postagem_pai <> 0
AND C.id_autor = (SELECT id_autor FROM postagem where id_postagem = c.postagem_pai AND id_autor = u.id_autor) 

If it is not correct, how should I make these conditions?

NOTE: I am using SQL SERVER

Answer 1

You can use a "Self Join" to filter posts where the author of the post is equal to the author of the parent post:

SELECT 
   pFilho.id_postagem, pFilho.titulo, a.nome
FROM
   postagem pFilho
   INNER JOIN postagem pPai
      ON pFilho.postagem_pai = pPai.id_postagem
      AND pFilho.id_autor = pPai.id_autor
   INNER JOIN autor a
      ON pFilho.id_autor = a.id_autor
WHERE
   pFilho.postagem_pai <> 0 

This last condition is redundant, since there is probably no posting with id_postagem zero.

Answer 2

Thinking of a forward-looking approach, I thought you might want to work with a framework that allows you to have "grandchildren" from your posts. For that I did the following script insertion:

CREATE TABLE autor(
  id_autor INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
  nome     VARCHAR(100)
);
go

CREATE TABLE postagem(
  id_postagem  INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
  titulo       VARCHAR(100),
  id_autor     INT,
  postagem_pai INT,
  CONSTRAINT fk_postagem_autor FOREIGN KEY (id_autor) REFERENCES autor (id_autor),
  CONSTRAINT fk_postagem_postagem FOREIGN KEY (postagem_pai) REFERENCES postagem (id_postagem)
);
go

-- Inserções
DECLARE @id_autor     INT,
        @postagem_pai INT;

-- Insere os autores
INSERT INTO autor(nome)
VALUES('Autor01');

SET @id_autor = SCOPE_IDENTITY();

INSERT INTO autor(nome)
VALUES('Autor02');

-- Insere o pai
INSERT INTO postagem(titulo, id_autor)
VALUES('post_pai', @id_autor);

SET @postagem_pai = SCOPE_IDENTITY();

-- Insere filhos
INSERT INTO postagem(titulo, id_autor, postagem_pai)
VALUES('post_filho1', @id_autor, @postagem_pai),
      ('post_filho2', @id_autor, @postagem_pai),
      ('post_filho3', @id_autor, @postagem_pai);

SET @postagem_pai = SCOPE_IDENTITY();

-- Insere netos
INSERT INTO postagem(titulo, id_autor, postagem_pai)
VALUES('post_neto3.1', @id_autor, @postagem_pai)
go

Which produces the following result:

Author table

Posting table

Using WITH

So to produce the selection of all posts I used the expression WITH of SQL Server.

WITH common_table_expression

Specifies a temporary named result set, known as a CTE (common table expression). It is derived from a simple query and defined in the execution scope of a single SELECT, INSERT, UPDATE or DELETE statement. This clause can also be used in a CREATE VIEW statement as part of the SELECT statement that defines it. A common table expression can include references to itself. It is what we call the common recursive table expression.

WITH conjunto AS(
  -- Aqui vai a seleção do registro base que você desejado (Também chamado de "archor")
  SELECT pai.*,
         1 AS nivel
    FROM postagem pai
   -- Aqui você insere o parâmetro que dirá qual postagem é a raiz
   WHERE pai.id_postagem = 1
  UNION ALL
  -- Note que abaixo a select tem um union na declaração do "WITH"
  SELECT filho.*,
         pai.nivel + 1 AS nivel
    FROM postagem filho
         -- Aqui se aplica a recursão
         INNER JOIN conjunto pai ON pai.id_postagem = filho.postagem_pai
)
SELECT conj.*
  FROM conjunto conj

RESULT:

Answer 3

I think if you use left Join to relate the two tables it will be simpler:

SELECT p.id_postagem, p.titulo, a.nome
FROM postagem p 
LEFT JOIN autor a
  ON p.id_autor = a.id_autor
WHERE postagem_pai <> 0
  AND p.id_autor = p.postagem_pai