get lookup 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

  • 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)
    Intro to Tally Tables and Functions

  • 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/

  • 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

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

    Sorry about the confusion

  • 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

  • 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

  • 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

  • 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

  • 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/

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

  • 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’! **
  • Getting error message: conversion failed when converting _A to int

  • 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’! **
  • Thank you...I really appreciate your help

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

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