Simon Mosbjerg Mørch Simon Mosbjerg Mørch - 2 months ago 5
MySQL Question

MySQL - Get results that don't match

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
.

Answer

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,  '%' )
)