SQLTeam.com | Weblogs | Forums

Data match from same table

#1

Hi,
Trying to find match from same lookup table
declare @lookup table(Id int, code varchar(50), description varchar(50))

insert into @lookup values(1,'UK','United Kingdom')
insert into @lookup values(2,'PS','PrivatSale')
select * from @lookup

  declare  @datatable table(custid int, langcode varchar(50), salecategory varchar(50))
   insert into @datatable values(101,'UK','PS')
   
select * from @datatable

-- Expected result

Custid    Country       Category
101   United Kingdom, PrviateSale
#2
SELECT
	d.custid,
	L1.description AS Country,
	L2.description AS Category
FROM
	@datatable AS d
	OUTER APPLY (SELECT TOP (1) description FROM @lookup AS L WHERE L.code = d.langcode ) AS L1
	OUTER APPLY (SELECT TOP (1) description FROM @lookup AS L WHERE L.code = d.salecategory) AS L2
#3

by the way your design approach smells very suspicious. You are co mingling disparate data types in the same column.