How to Exclude Specific Row from Query in MySQL

How can I exclude a certain row from an MySQL query

If you know the exact id (say, id = 1 for example), do this:

SELECT * FROM imdb WHERE genres LIKE 'Adventure' AND id <> 1 ORDER BY id DESC LIMIT 10

See this SO post.

How to exclude rows when a certain condition is met in MySQL

You can group by participant and use conditional aggregation in the HAVING clause to set the conditions:

SELECT p.id, p.name
FROM Participants p INNER JOIN Registrars  r
ON p.id = r.person_id
WHERE r.event_id IN (1, 2)
GROUP BY p.id, p.name
HAVING MAX(r.event_id = 1) = 1 
   AND MAX(r.event_id = 2) = 0 

You can extend the code by adding more conditions in a similar way.

How to exclude particular row id from query results?

You need to write query like below :

SELECT * FROM `Member` WHERE Member.member_id NOT IN ('admins' , 'admin','manager');

MySQL exclude rows subquery

You can do it with not exists:

select * 
from test t
where status = 'arriving'
  and not exists (select 1 from test
                  where trip = t.trip and status = 'departing')

I'm not sure if you want the subquery's condition maybe like this:

where trip = t.trip and status = 'departing' and date > t.date

mysql WHERE CLAUSE to exclude rows based on two different values in same column

There is no form of LIKE for comparing to multiple patterns.

As @vkp mentioned in a comment (I don't know why they don't post an answer), your condition won't do what you intend:

WHERE option_name NOT LIKE '%_transient_%' OR '%mm%' 

It's not a syntax error in MySQL, because OR can use any expression as operands. Zero values count as false, nonzero values count as true. So your condition is equivalent to:

WHERE (option_name NOT LIKE '%_transient_%') OR ('%mm%')

Which is logically equivalent to:

WHERE (option_name NOT LIKE '%_transient_%') OR (true)

Which will be true on every row, because a true value OR'd together with any other expression will result in true.

Your condition should be this:

WHERE option_name NOT LIKE '%_transient_%' AND option_name NOT LIKE '%mm%'

How to exclude row that is matched 100% in mysql full text search?

How about just adding another condition to the where clause?

WHERE 
    MATCH (title) AGAINST ('+MySQL' IN BOOLEAN MODE)
    AND title <> 'MySQL'

Exclude rows from select if that row having more than 1 same value / duplicate

Just another option is to use WITH TIES in concert with a window functions

Example

Select top 1 with ties *
 From  YourTable
 Order By sum(1) over (partition by FK)

Returns

ID  Amount  Status  FK
1   -1000   T       10
2   -1500   T       11
3   -100    T       12
4   -200    T       13

---

Related Topics