MySQL - Get results that don't match

Question

I am trying to build a query that will return all rows that's not like a query.

I just cant get it to work.

The query is:

SELECT  *
FROM  EmailHistory
WHERE Subject not like
(
SELECT Date, Subject, Sender, Checks.Check FROM EmailHistory JOIN Checks ON EmailHistory.Subject LIKE CONCAT(  '%', Checks.Check,  '%' )
)

So basically I want to get all rows from EmailHistory, that I do not get when running:

SELECT Date, Subject, Sender, Checks.Check FROM EmailHistory JOIN Checks ON EmailHistory.Subject LIKE CONCAT(  '%', Checks.Check,  '%' )

I get the following error when I try to run the query: #1241 - Operand should contain 1 column(s)

The query

SELECT Date, Subject, Sender, Checks.Check FROM EmailHistory JOIN Checks ON EmailHistory.Subject LIKE CONCAT(  '%', Checks.Check,  '%' )

is working as it should, I get query of all queries where the subject matches the fields that I check from the table Checks.

you can't return more than one field in the sub-select... how should mysql compare 5 fields agains 1 field? use WHERE field NOT IN (SELECT field .. WHERE ..) — Honk der Hase, Sep 21 '16 at 20:41
Like if for match a substring inside a string not for scan table and column — ScaisEdge, Sep 21 '16 at 20:42

score 0 · Accepted Answer · answered Sep 21 '16 at 20:42

You can't use LIKE to match with a table. The nested query returns a table with multi columns.

You are trying to match the column 'Subject' with a table.

The query you need is:

SELECT  *
FROM  EmailHistory
WHERE Subject NOT IN
(
SELECT Subject FROM EmailHistory JOIN Checks ON EmailHistory.Subject LIKE CONCAT(  '%', Checks.Check,  '%' )
)

MySQL - Get results that don't match

1 Answers1