sqljoinconcatenationfieldmultiple-columns

JOIN ON condition with common columns as concatenation of fields


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!


Solution

  • 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.