SQLTeam.com | Weblogs | Forums

Join two tables with non-matching keys


#1

I have two tables like this:

Items

ItemID (PK)	Title
101	        iPhone 5
102	        iPhone 6
103	        Blackberry Storm
104	        Samsung Galaxy S1
105	        Samsung Galaxy S6

Sales

 SaleID (PK)	Reference	        Title
  1	            103-B00	          Blackberry Storm
  2	            101-IK5		      iPhone 5
  3	            105-ISG		  	  Samsung Galaxy S6
  4	            104-UN3		  	  Samsung Galaxy S1
  5	            101-IK5		  	  iPhone 5
  6	            102-HWB		 	  iPhone 6
  7	            105-ISG		      Samsung Galaxy S6
  8	            101-IK5		  	  iPhone 5
  9	            102-HWB		      iPhone 6

I want to join them using "ItemID" column in Items table and "Reference" column in Sales table. As you can see, the values of two columns are not matching, but the part before the hyphen in each value of "Reference" column is matched with values in "ItemID" column. How can I join them? I know it's odd to have two columns with such values, but I did not create this table and must play with what's available. Thanks


#2

Maybe:

select * /* specify which columns you want */
  from sales as s
       left outer join items as i
                    on concat(i.itemid,'-')=left(s.reference,len(i.itemid)+1)
;

#3

you can use below methods:
1:
select t2.title from sales t2 inner join items t1 on t1.itemid=left(t2.itemid,3)
2:
select t2.title from sales t2 inner join items t1 on t1.itemid=substring((convert(varchar(50),t2.itemid )),1,3)