CODEWITHSUNDEEP
mysql
Leza Rivolta
Leza Rivolta

Reputation: 1

MySQL find articles which not in deleted category

I'm trying to search for articles, which are not in a deleted category. I use soft delete, categories are just marked as deleted in database (flag_deleted=1). When a category is deleted, the childs are not marked as deleted too. Thats why it is possible that the article is assigned to a category which is not marked as deleted, but the direct assigned category is child of a already deleted category.

I found a solution to search the tree up to check, if a category is child of a deleted category.

I have the following database structure.

article
------
id
category_id
title
flag_deleted


category
------
id
id_parent
name
flag_deleted

I am using the following query:

SELECT
    `ID`,
    `title`,
FROM
    `article` a
WHERE
    `flag_deleted`=0
AND
(
    SELECT sum(case when T2.flag_deleted = 1 then 1 else 0 end) as deleted_count
        FROM (
            SELECT
                @r AS _id,
                (SELECT @r := id_parent FROM category WHERE id = _id) AS id_parent,
                @l := @l + 1 AS lvl
            FROM
                (SELECT @r := id_category, @l := 0) vars,
                category m
            WHERE @r <> 0
        ) T1
    JOIN
        category T2
    ON
        T1._id = T2.ID
) = 0

The problem with this solution is, i can not access the id_category in subquery. When i put manually id instead of id_category in subquery, it works as expected.

Upvotes: 0

Views: 224

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

I try to search articles, which are not in a deleted category. I have the following database structure.

This sounds like NOT EXISTS or NOT IN:

select a.*
from articles a
where not exists (select 1
                  from category c
                  where c.id = a.category_id and c.flag_deleted = 1
                 );

I have no idea why your version is so complicated. If you have another question, you should ask it as another question.

Upvotes: 0

Haninder Singh
Haninder Singh

Reputation: 618

SELECT *
FROM table_name
WHERE col_name NOT IN("<your value>");

Upvotes: 0

Related Questions