Search code examples
mysqlinner-joinwhere-clausesql-like

SQL INNER JOIN with LIKE and a WHERE

I have 2 tables. One called DocumentStatistics one called Documents. DocumentStatistics has a field called filename that contains a filename such as abcdef. Documents on the otherhand has a similar naming scheme but because it also holds old files they are numbered (for instance it might have abcdef1 and abcdef2). Only the latest document will be marked as islive=-1.

I would like to display the data from DocumentStatistics plus the correct cell from the Documents table. Can someone help me? This is what I have so far:

SELECT DocumentStatistics.*,Documents.tmpname
FROM DocumentStatistics
INNER JOIN Documents
ON DocumentStatistics.filename LIKE 'Documents.filename%'
WHERE Documents.islive=-1
Solution

  • Probably you can do it with SUBSTRING. Something like this:

    SELECT DocumentStatistics.*,Documents.tmpname
FROM DocumentStatistics
LEFT JOIN Documents
ON SUBSTRING(DocumentStatistics.filename,start,length)= SUBSTRING(Documents.filename,start,length)
WHERE Documents.islive=-1