Help writing a mySQL query - Matching a column from one table to a column in another

[bradical1379]

I am attempting to write a query that matches a titles of some material I have in a database to a list in another table of authors, and then spit out the results. The issue is that sometimes the author name may be along the lines of CJ, C.J. or C J Watson, so its impossible to get an exact match all of the time.

Any ideas how I could match up the data from the author tables to match the data in the title fields?

SELECT AVG( d.price ) AS price , COUNT(d.id) AS total, c.author
FROM data d
INNER JOIN counts c ON c.setid = d.setid
WHERE d.title LIKE concat( '%', c.author, '%' )
AND d.id = '1'
AND d.filter = 'today'
AND d.date >= unix_timestamp(now() - interval 1 day)
GROUP BY c.author
ORDER BY price DESC

[r937]

try SOUNDEX() which ignores punctuation

[bradical1379]

try SOUNDEX() which ignores punctuation

Would a fulltext search work better?

Something like this?

SELECT AVG( d.price ) AS price , COUNT(d.id) AS total, c.author
FROM data d
INNER JOIN counts c ON c.setid = d.setid
WHERE MATCH(d.title) AGAINST(c.author IN BOOLEAN MODE)
AND c.id = '1'
AND d.filter = 'today'
AND d.date >= unix_timestamp(now() - interval 1 day)
GROUP BY c.author
ORDER BY price DESC

[r937]

Would a fulltext search work better?

hey i just met you
and that's not crazy
so take your idea
and test it maybe

[bradical1379]

hey i just met you
and that's not crazy
so take your idea
and test it maybe

Error: #1210 - Incorrect arguments to AGAINST

Are we allowed the have c.author value in the against clause?

[guelphdad]

does c.author have a FULLTEXT index? If it doesn't you can't.

[bradical1379]

does c.author have a FULLTEXT index? If it doesn't you can't.

Yes, as does d.title.