Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Exclude rows where column matches another column without subquery

I have a result set of parent categories and subcategories. I want to exclude all parent categories if a subcategory exists in the results.

What I would do is using a NOT IN in the query, but that requires a subquery identical to the main query.

As I’m using match against and possibly other filters in a later stage, I feel there should be an easier way to accomplish this.

The query with the full results is this:

SELECT id,cat_name,cat_parent FROM transport
WHERE MATCH (cat_name) AGAINST ('car*' IN BOOLEAN MODE)

Result:

+-----+------------------+------------+
| id  | cat_name         | cat_parent |
+-----+------------------+------------+
| 297 | carnaval         | 289        |
| 350 | cars & boats | 0          |
| 351 | cars             | 350        |
+-----+------------------+------------+

I want to exclude the parent category ‘cars & boats’ as the subcategory ‘cars’ exists in the results.
To accomplish this, I use NOT IN:

SELECT id,cat_name,cat_parent FROM transport
WHERE MATCH (cat_name) AGAINST ('car*' IN BOOLEAN MODE)
AND id NOT IN(
    SELECT cat_parent FROM transport
    WHERE MATCH (cat_name) AGAINST ('car*' IN BOOLEAN mode)
)

This works great, but I don’t like it that I have to copy the query in the NOT IN. It feels redundant, even though all the answers to similar questions on SO lead to the same answer, using a subquery with either NOT IN or NOT EXISTS.

I have tried AND id != cat_parent and AND id NOT IN (cat_parent), but that just results in the same output.

Is there any way to accomplish this by just using the result set?

Tags:

2

Answers


  1. 0

    You can try a query like this:

    SELECT t1.id, t1.cat_name, t1.cat_parent
FROM transport t1
LEFT JOIN transport t2
ON t1.id = t2.cat_parent AND MATCH(t2.cat_name) AGAINST('car*' IN BOOLEAN MODE)
WHERE MATCH(t1.cat_name) AGAINST('car*' IN BOOLEAN MODE)
AND t2.cat_parent IS NULL;

    If you only want to "write" the WHERE clause once so that you don’t have to do it twice and keep it the same, you can achieve this with CTE. e.g. like this:

    WITH filtered_transport AS (
    SELECT id, cat_name, cat_parent
    FROM transport
    WHERE MATCH (cat_name) AGAINST ('car*' IN BOOLEAN MODE)
)
SELECT t1.id, t1.cat_name, t1.cat_parent
FROM filtered_transport t1
LEFT JOIN filtered_transport t2
ON t1.id = t2.cat_parent
WHERE t2.cat_parent IS NULL;
    Login or Signup to reply.
  2. 0

    If your sample is representative can’t you just count by parent filtering on car

    DROP TABLE IF EXISTS T;

create table t(id  int, cat_name varchar(20),  cat_parent int,fulltext(cat_name));
insert into t values
( 297 , 'carnaval'         , 289        ),
( 350 , 'cars & boats' , 0          ),
( 351 , 'cars'             , 350        ),
(200,'plane',0);

select case when cat_parent = 0 then id else cat_parent end as p,
       count(*)
from t
WHERE MATCH (cat_name) AGAINST ('car*' IN BOOLEAN mode)
group by p having count(*) = 1;

    https://dbfiddle.uk/sgU0RFip

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search