Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

get lookup value

Status
Not open for further replies.

CrystalProgDev

Programmer
Oct 1, 2010
69
US
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
 
Code:
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
 
Getting error message: conversion failed when converting _A to int
 
Why your Table1.Col3 is Int?
Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top