MySQL, searching words from one line to another line

Question

I have no idea if this is possible in a single SQL statement. I read all day and can't find solutions and be frank, my experience is only at the tip of the iceberg when it comes to SQL.

I have two different tables, say: elements and clients. Both tables have a TEXT type (Notes) field containing words separated by spaces and CR / LF pairs.

I need to select all lines (elements. *), Where any of the words in the customers.note file matches any word in items.note

Example:

Table items
ID NOTES
-- ----------
1  bag candle fork
2  max kielland
3  plate knife fork
4  wheel brownbag

Table customers
ID NOTES
-- ----------
1  brown candle fork

As a result, I want:

Items.ID Items.Note
-------- ----------
1        bag candle fork
3        plate knife fork

I looked at IN and LIKE (as separate solutions, not as a pair), but they don't seem to solve the problem. If this helps, I can make sure that all spaces are replaced with ","

, LIKE%..%, :

Items.ID Items.Note
-------- ----------
1        bag candle fork
3        plate knife fork
4        wheel brownbag

, .

, , MySQL PHP.

.

---

Update

OMG Ponies MATCH..AGAINST PHP .

$SQLSet = mysql_query("
  SELECT DISTINCT i.*
  FROM items i
  JOIN wanted w ON MATCH(i.notes)
  AGAINST(\"".$SQLRowWanted['Notes']." +'".$SQLRowWanted['Name']."'\")
");

$SQLRowWanted . Name '...' +, .

Answer 1

(FTS). MySQL FTS, MATCH ... AGAINST, (). MySQL MyISAM, CHAR, VARCHAR TEXT.

SELECT i.id,
       i.notes
  FROM ITEMS i
  JOIN CUSTOMERS c ON MATCH(i.notes) AGAINST (c.notes IN BOOLEAN MODE)

FTS:

• Sphinx
• Solr ( lucene)

Answer 2

, . "1: " :

notesID | noteText
1         bag
1         candle
1         fork
2         max
2         kielland
...

SELECT * FROM customers, items WHERE customers.noteText = items.noteText;

, .