JOIN ON condition with common columns as concatenation of fields

Question

I would like to make a JOIN ON but I have no common columns.

The only relationship I have is a column in table B with a concatenation of 2 columns in table A:

Table A:

id	name	surename
1	Joe	McKlein
2	Mark	Johnson

Table B:

completeName	sonName
Joe McKlein	Mike
Joe McKlein	Richard
Mark Johnson	Peter
Joe McKlein	Patrick

I would like to get:

id	completeName	sonName
1	Joe McKlein	Mike
1	Joe McKlein	Richard
2	Mark Johnson	Peter
1	Joe McKlein	Patrick

I thought in something like:

SELECT a.id, b.completeName, b.sonName FROM tableA AS a JOIN tableB AS b ON b.completeName = CONCAT(a.name, ' ', a.surename)

but in 'real tables' with lots of registers (no this ones in the example) I get a 'time expiring error'. Any advice?

Thanks!

Answer 1

Your code looks fine. The slow performance is due to the bad database design, and nothing you do with your query can speed things up.

The fact that you're getting a 'timeout' makes me wonder if you're writing the query through a 3rd party tool that has a specific timeout. If that's the case, you might be able to change the timeout settings yourself in whatever tool you're using, to basically wait until the query finishes.

If the database server is actually timing out, that could be a user setting that the admins can increase for you. They might not be interested in fixing the table to add the proper foreign keys, but if you are annoying enough, they might just simply increase the timeout or increase the performance profile of your user account so this query can finish before the timeout.

Good luck!

P.S. One tip that might help you do ugly joins like this, is force UPPER() or LOWER() on both sides of the join to prevent case sensitivity from giving you a mismatch when there is a mistake in capitalization. This won't speed things up, but is just a tip for making this type of join more accurate.