mysql - LEFT JOIN two tables with columns that almost match

Question

I have two tables in which I need to join. Problem is the only fields I can use in each table are not totally identical, but it is all I can use.

Voting.movie_id = a123456789
Movies.movie_id = 123456789

As you can see one has the letter 'a' in the front. The rest totally matches and always will. Its just that 'a' in front that makes it different.

I was hoping there was a way to do this with a join so I tried

SELECT * 
FROM voting 
RIGHT JOIN movies ON voting.movie_id = 'a'+movies.movie_id 
ORDER BY voting.ID DESC 
LIMIT 40

SELECT * 
FROM voting 
RIGHT JOIN movies ON voting.movie_id = '%'+movies.movie_id 
ORDER BY voting.ID DESC 
LIMIT 40

This did not work, is there a way to do this?

I do not think this question should be marked as much as a duplicate of http://stackoverflow.com/questions/5975958/mysql-concatenation just because I had no idea that `Concatenation` meant what it did. For those of us who do not know how to word this particular syntax this might help others get the answer. — Cesar Bielich, Nov 26 '14 at 20:27
You knew that you wanted to concatenate `a` and the movie ID, you just didn't know the correct syntax for that. The answer in that question solves it, doesn't it? — Barmar, Nov 26 '14 at 21:31
The questions may not be identical, but that's just because you didn't know how to express the question correctly. The answer is the same. — Barmar, Nov 26 '14 at 21:32

score 2 · Accepted Answer · answered Nov 26 '14 at 20:05

2

In MySQL, you concatenate strings using the CONCAT() function, not +.

SELECT * 
FROM voting 
RIGHT JOIN movies ON voting.movie_id = CONCAT('a', movies.movie_id)
ORDER BY voting.ID DESC 
LIMIT 40

answered Nov 26 '14 at 20:05

Barmar

741,623
53
500
612

mysql - LEFT JOIN two tables with columns that almost match

1 Answers1