get lookup value

[CrystalProgDev]

I have two tables, lets say Table 1 and Table 2 as below

Table 1:
Col1 Col2 Col3
1 _A 11
2 _B 12
3 _C 12
4 _A 11

Table 2:

ID Val
_A A
_B B
_C C
11 AA
12 BB
13 CC

I need an output From Table1 and Table 2 as below

1 A AA
2 B BB
3 C BB
4 A AA

I am very new to SQL Server. Can you please help me in getting this output?

Thank you in advance

 

[bborissov]

SELECT Table1.Col1
      ,MAX(Table2.Val) AS Col2
      ,MAX(Table3.Val) AS Col3
FROM Table1
INNER JOIN Table2        ON Table1.Col2 = Table2.Id
INNER JOIN Table2 Table3 ON Table1.Col3 = Table3.Id
GROUP BY Table1.Col1

Borislav Borissov
VFP9 SP2, SQL Server

 

[CrystalProgDev]

Getting error message: conversion failed when converting _A to int

 

[bborissov]

Why your Table1.Col3 is Int?

SELECT Table1.Col1
      ,MAX(Table2.Val) AS Col2
      ,MAX(Table3.Val) AS Col3
FROM Table1
INNER JOIN Table2        ON Table1.Col2 = Table2.Id
INNER JOIN Table2 Table3 ON CAST(Table1.Col3 as varchar(10))= Table3.Id
GROUP BY Table1.Col1

Borislav Borissov
VFP9 SP2, SQL Server