CODEWITHSUNDEEP
sqlsql-servert-sqljoinconcatenation
Niras
Niras

Reputation: 455

SQL Query Concatenate two columns during Inner JOIN

I have table A and table B with Table A having several columns including A1 and A2. Table B too has several columns. My query requires me to concatenate the values in A1 and A2 and then do an inner join on B1.

Example:

Select * 
From A
INNER JOIN B
ON CONCAT(A1,A2) = B1.

Apparently this is not how it should work. Can someone please give me a hand in this query?

Thanks.

Upvotes: 8

Views: 78278

Answers (2)

Pedro Ferreira
Pedro Ferreira

Reputation: 649

Sample taken from

Table Geography

region_name store_name
East    Boston
East    New York
West    Los Angeles
West    San Diego

Example 1: For MySQL/Oracle: 

    SELECT CONCAT(region_name,store_name) FROM Geography 
    WHERE store_name = 'Boston';
Result: 'EastBoston'

Example 2: For Oracle: 

    SELECT region_name || ' ' || store_name FROM Geography 
    WHERE store_name = 'Boston';
Result: 'East Boston'

Example 3: For SQL Server: 

    SELECT region_name + ' ' + store_name FROM Geography 
    WHERE store_name = 'Boston';
Result: 'East Boston'

Starting from this, you can adapt to two tables without much issue. In doubt, use a virtual Table to make things more readable.

If in doubt check this other question which has been answered for more details.

StackOverFlow Similar Question

Upvotes: 2

Andrey Gurinov
Andrey Gurinov

Reputation: 2885

Try this:

Select *  
From A 
INNER JOIN B 
ON A1 + A2 = B1

Upvotes: 21

Related Questions