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