get lookup data

Question

get lookup data

sqlsdev

SSC Veteran

Points: 263
More actions
June 2, 2013 at 8:15 pm

#273363

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

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 1

sqlsdev (6/2/2013)
I have two tables, lets say Table 1 and Table 2 as below
Table 1:
Col1 Col2
1 _A
2 _B
3 1
4 2
Table 2:
ID Val
_A A
_B B
_C C
1 AA
2 BB
3 CC
I need an output From Table1 and Table 2 as below
1 _A A
2 _B B
3 1 AA
4 2 BB
I am very new to SQL Server. Can you please help me in getting this output?
Thank you in advance

This is just a simple join between Table1.Column2 and Table2.ID. Give it a try. If you don't understand it, look up "Joins" in Books Online (the "help" system in Sql Server... press the {f1} key in SSMS to get there).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

Try this:

SELECT T1.Col1, T1.Col2, T2.Val

FROM table1 T1

INNER JOIN table2 T2 on T1.col2 = T2.ID

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

sqlsdev SSC Veteran Points: 263 More actions · Answer 3

Sorry... I have the data in table something like this

Table 1:

Col1 Col2 Col3

1 _A

2 _B

3 1

4 2

Table 2:

ID Val

_A A

_B B

_C C

1 AA

2 BB

3 CC

I need an output From Table1 and Table 2 as below

1 _A A

2 _B B

3 1 AA

4 2 BB

sqlsdev SSC Veteran Points: 263 More actions · Answer 4

_A, _B are in Col3 and 1,2 are in Col2 in Table 1.

Sorry about the confusion

sqlsdev SSC Veteran Points: 263 More actions · Answer 5

Simply table 1 with the following data

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

Neel 7777 Right there with Babe Points: 778 More actions · Answer 6

sqlsdev (6/2/2013)
I have two tables, lets say Table 1 and Table 2 as below
Table 1:
Col1 Col2
1 _A
2 _B
3 1
4 2
Table 2:
ID Val
_A A
_B B
_C C
1 AA
2 BB
3 CC
I need an output From Table1 and Table 2 as below
1 _A A
2 _B B
3 1 AA
4 2 BB
I am very new to SQL Server. Can you please help me in getting this output?
Thank you in advance

use databasename

select a.Col1, a.Col2, b.Val from table2 as b

inner join table1 as a

on a.col1=b.ID

Neel 7777 Right there with Babe Points: 778 More actions · Answer 7

Try this

use databasename

select a.Col1, a.Col2, b.Val from table2 as b

inner join table1 as a

on a.col1=b.ID

sqlsdev SSC Veteran Points: 263 More actions · Answer 8

Sorry... Here is the table structure

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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 9

sqlsdev (6/3/2013)
Sorry... Here is the table structure
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 think you yourself is confused what is the table structure and what output is required.....

Please take your time and then post your query, it will save time of everyone..

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

sqlsdev SSC Veteran Points: 263 More actions · Answer 10

the above one is the final structure of table... sorry about my confusion

HanShi SSC-Dedicated Points: 33507 More actions · Answer 11

You'll need to join the seond table twice to the first table, like:select

table1.col1

, table2_a.val

, table2_b.val

from table1

inner join table2 as table2_a

on table1.col2 = table2_a.id

inner join table2 as table2_b

on table1.col3 = table2_b.id

The table2 has given alternate names to be distinctive in both JOINs and the SELECT.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

sqlsdev SSC Veteran Points: 263 More actions · Answer 12

Getting error message: conversion failed when converting _A to int

HanShi SSC-Dedicated Points: 33507 More actions · Answer 13

sqlsdev (6/3/2013)
Getting error message: conversion failed when converting _A to int

Then you'll need to cast the columns to the same type. I don't know how the column type is defined, so below are both columns in each JOIN explicitly declared:select

table1.col1

, table2_a.val

, table2_b.val

from table1

inner join table2 as table2_a

on cast(table1.col2 as varchar(2)) = cast(table2_a.id as varchar(2))

inner join table2 as table2_b

on cast(table1.col3 as varchar(2)) = cast(table2_a.id as varchar(2))

The table2 has given alternate names to be distinctive in both JOINs and the SELECT.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

sqlsdev SSC Veteran Points: 263 More actions · Answer 14

sqlsdev

SSC Veteran

Points: 263

June 3, 2013 at 4:25 am

#1620456

Thank you...I really appreciate your help