0

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
Improve this question
1
  • I suggest working with primary and foreign keys. Commented Jan 14, 2015 at 21:33

2 Answers 2

Reset to default
1

like is fine. You just need to call it correctly:

SELECT ds.*, d.tmpname FROM DocumentStatistics ds INNER JOIN Documents d ON ds.filename LIKE CONCAT(d.filename, '%') WHERE d.islive = -1;

Your version was calling it with a constant string, rather than the name of a column.

I added table aliases to the query. They make the query easier to write and to read.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

CONCAT is something I have not encountered before. Thanks for adding something to my toolbox.
0

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
Improve this answer

2 Comments

I am very new to this website. How do I accept yours as an answer?
There's a button somewhere to the left of my answer. I cannot really tell :)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.