MySQL PHP如果表中的关键字短语在句子表显示结果中不存在

Question

I have two tables: Sentences, Negatives.

I would like to select column Sentences.sentence that does not contain any record in Negatives.negphrase.

There are 200k records in Sentences and 50k records in Negatives.

Sentences.sentence Sample Data
=============================

 - university lab on campus
 - laboratory designs
 - lab coats
 - math lab
 - methane production
 - meth lab

Negatives.negphrase Sample Data
======================================

 - coats
 - math lab
 - meth

Desired Result Set
==================

 - university lab on campus
 - laboratory designs
 - methane production

I tried using the result of a different question of mine but the database timed out:

SELECT Sentences.sentence
FROM Sentences, Negatives
GROUP BY Sentences.sentence
HAVING (((Max(InStr(" " & sentence & " "," " & negphrase & " ")))=0));

MY ANSWER

So I'll give the correct answer to Richard b/c his solution does work for smaller record sets, but not large ones. Here is the PHP code I used to put all negative keywords in an array, then loop through that array with an UPDATE clause to mark a new column 'negmatch' in the Sentences table. I'll use that in another WHERE clause to select Sentences.sentence WHERE negmatch <> 1.

I only have to run this code once for all negphrases, then when I add additional keywords I use the same code but without a loop to search the sentences again (code not shown below). The code takes 6.5 minutes to loop through 2800 UPDATE clauses so the initial load is pretty long, but once it is done it doesn't have to be done again.

<?php
$mysqli = new mysqli("localhost", "myuser", "myuserpassword", "database");

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}

if ($result = $mysqli->query("SELECT negphrase FROM negatives")) {  
    $row_cnt = $result->num_rows;
    printf("Negative keywords have %d rows.
", $row_cnt); //print count of rows

    while($row = $result->fetch_assoc()){ //loop through all results by row
        foreach( $row  AS $value ) {
        $negative[] = $value;
    }
}


    /* free result set */
    $result->close();

    $data = array_values($negative); // get only values
    $data = array_filter($data);
    $datacount = 1;
    foreach($data as $val) { //loop through array to build MySQL WHERE clause


            $updatequery = "UPDATE Sentences SET negmatch=1 WHERE sentence REGEXP '[[:<:]]" . trim($val) . "[[:>:]]'";
            echo $updatequery  . "<br />";

            mysqli_query($mysqli,$updatequery) or die (mysqli_error($mysqli));
            echo $datacount . " " . trim($val) ."<br />";
            $datacount++;

        }

}
$mysqli->close();


    unset($result, $row, $mysqli,$value,$negative,$data,$val,$updatequery,$datacount,$row_cnt);

?>

Answer 1

Use negative left join, this will return only rows from Senteces table that doesn't match Negatives table based on rule

select * from Sentences s 
left join Negatives n 
on (concat(" ",s.sentence," ") like concat("% ",n.negphrase," %"))
where n.negphrase is null

tested on data bellow

CREATE TABLE IF NOT EXISTS `Negatives` (
  `negphrase` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `Negatives` (`negphrase`) VALUES
('coats'),
('math lab'),
('meth');

CREATE TABLE IF NOT EXISTS `Sentences` (
  `sentence` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `Sentences` (`sentence`) VALUES
('university lab on campus'),
('laboratory designs'),
('lab coats'),
('math lab'),
('methane production'),
('meth lab'),
('testing sentence');