3

I have no idea if this is possible to do in one SQL statement. I have been reading all day and can't seem to find a solution and to be frank, my experience is only at the tip of the iceberg when it comes to SQL.

I have two different tables, lets say: items and customers. Both tables have a field (Notes) of type TEXT containing words separated with spaces and CR/LF pairs.

I need to select all rows (items.*) where any of the words in customers.note 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

The result I want would be:

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

I have been looking at IN and LIKE (as separate solutions, not together as a pair) but they don't seem to quite solve the problem. If it is to any help, I can make sure all spaces are replaced by ','

In the long end it would be even more perfect if the compare could be made as the LIKE %..% producing a result like this:

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

as you can see the brown get a hit on brownbag.

As I said I don't know if this is possible in MySQL without splitting up the query in several questions with some PHP processing.

Thank you.

Update

With the help of OMG Ponies MATCH..AGAINST suggestion and some PHP I solved it.

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

This is running inside a loop with $SQLRowWanted from an earlier call. By encapsulating the Name column in '...' and adding a + before it, I get a higher match rate on the full name as well.

Max Kielland
  • 5,627
  • 9
  • 60
  • 95
  • There is no combination of LIKE & IN in SQL: http://stackoverflow.com/questions/3014940/is-there-a-combination-of-like-and-in-in-sql – OMG Ponies Dec 12 '10 at 18:08
  • I didn't mean IN and LIKE as a pair, but as separate solutions I already tried. Is that unclear? Okay I will edit and again, thank you for your help with my questions so far ;) – Max Kielland Dec 13 '10 at 03:28

2 Answers2

3

I would look at using Full Text Search (FTS). MySQL has native FTS functionality using the MATCH ... AGAINST function, but it requires that a full text index exist on the column(s). MySQL's Full Text index can only be applied to MyISAM tables, for CHAR, VARCHAR, or TEXT columns. 

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

Otherwise, you'll need to look at 3rd party FTS:

  • Sphinx
  • Solr (lucene based)
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • I changed both tables to MyISAM table engines and created a FULLTEXT index for the 'Notes' column in both tables. As long I put a string in the AGAINST parameter it works fine 'SELECT DISTINCT i.* FROM items i JOIN customers c ON MATCH(i.notes) AGAINST("bag fork")'. But if I use the column, as in your example 'SELECT DISTINCT i.* FROM items i JOIN customers c ON MATCH(i.notes) AGAINST(c.notes)', I get the error "Incorrect arguments to AGAINST, ErrorNr. 1210" I have seen other having the same problem and it seems like AGAINST() only takes a STRING CONSTANT. – Max Kielland Dec 12 '10 at 23:57
  • @Max Kielland: Try adding the `IN BOOLEAN MODE` -- see my update. – OMG Ponies Dec 13 '10 at 00:47
  • 1
    Nope, your update didn't help. I have seen in some forums that it has to be a constant (http://forums.mysql.com/read.php?10,173549,173736#msg-173736). Some points out that a "prepared statement" would do the trick. Anyway, you pointed me in the right direction and I have solved AGAINST constant issue with PHP. Thank you! – Max Kielland Dec 13 '10 at 01:46
1

Your table is not normalised, which makes it harder. For each of "1: bag candle fork" you should have a seperate row:

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

Then you can simply do

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

You may need to create seperate tables with just the two columns shown above, and join them back to your customers and items table to look up more data.

FlightOfStairs
  • 123
  • 1
  • 7