CODEWITHSUNDEEP
sqlsql-serverjoin
Joshua
Joshua

Reputation: 21

SQL Join on partial column data (SQL Server)

Both columns are tinyint if it matters.

Table1

Version
--------
111115
222226
333337

Table2

ID  Year
--------
5   2015
6   2016
7   2017

I need to join on the ID from table 2 if it equals the last value in the version field from table 1.

Version 111115 would join to ID 5. I know how to select on things like this but is joining feasible? The result being:

111115  5  2015

TIA!

Upvotes: 2

Views: 1149

Answers (3)

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14699

Declare @tblTest as table
(
    id INT,
    yearName INT
)


Declare @tblVersion as table
(
    VersionNo INT
)

INSERT INTO @tblTest values(5,2015)
INSERT INTO @tblTest values(6,2016)
INSERT INTO @tblTest values(7,2017)

INSERT INTO @tblVersion values(111115)
INSERT INTO @tblVersion values(111116)
INSERT INTO @tblVersion values(111117)


select 
*,RIGHT(VersionNo,1) 
from @tblVersion

SELECT 
*
FROM @tblTest T1 
LEFT JOIN (SELECT RIGHT(VersionNo,1) AS VersionId,VersionNo FROM @tblVersion) T2 ON T2.VersionId=T1.id

Upvotes: 0

John D
John D

Reputation: 1637

Since they are both integer, you can try the modulus operator. It takes the remainder after division. So 111115 % 10 is 5. 

SELECT *
FROM Table1 t1
INNER JOIN Table2 t2 on t1.Version % 10 = t2.ID

Upvotes: 1

Aaron Dietz
Aaron Dietz

Reputation: 10297

You can use the RIGHT() function to do this:

SELECT *
FROM Table1 A
INNER JOIN Table2 B on RIGHT(A.Version,1) = B.ID

I would probably avoid having to do this very much though. It is a faulty database design. Perhaps add a column ID to table 1, and use:

UPDATE Table1
SET ID = RIGHT(Version,1)

Upvotes: 2

Related Questions