Query Help

• hawg

  SSCertifiable

  Points: 6044

  More actions

  June 4, 2007 at 2:12 pm

  #69025

  I'm embarrased to post this but I've got tunnel vision and someone has asked me for help quickly.

   

  I have two tables setup like the following:

   

  CREATE TABLE table_1(

        id_1  int,

        id_2  int,

        id_3  int)

   

  CREATE TABLE table_2(

        main_id           int,

        main_desc   varchar(250))

   

  INSERT INTO table_1

  VALUES(1, 2, 3)

   

  INSERT INTO table_2 VALUES(1, 'Description 1')

  INSERT INTO table_2 VALUES(2, 'Description 2')

  INSERT INTO table_2 VALUES(3, 'Description 3')

  INSERT INTO table_2 VALUES(4, 'Description 4')

  INSERT INTO table_2 VALUES(5, 'Description 5')

   

  I want to get the following info in one query:

   

   

  Col1  Col2              Col3  Col4              Col5  Col6

   

  1     Description 1     2     Description 2     3     Description 3

   

  I know this is probably very, very easy but I am just drawing a blank.  I have come up with a way to do it but it is not very good.

   

  Can someone tell me the “best” way to do this?

   

  Thanks.

  ----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 4, 2007 at 3:12 pm

  #710714

  Is this what you are looking for?

  CREATE TABLE dbo.table_1(

        id_1  int,

        id_2  int,

        id_3  int)

  CREATE TABLE dbo.table_2(

        main_id           int,

        main_desc   varchar(250))

  INSERT INTO table_1

  VALUES(1, 2, 3)

  INSERT INTO table_2 VALUES(1, 'Description 1')

  INSERT INTO table_2 VALUES(2, 'Description 2')

  INSERT INTO table_2 VALUES(3, 'Description 3')

  INSERT INTO table_2 VALUES(4, 'Description 4')

  INSERT INTO table_2 VALUES(5, 'Description 5')

  select * from dbo.table_1

  select * from dbo.table_2

  select

      a.id_1,

      b.main_desc,

      a.id_2,

      c.main_desc,

      a.id_3,

      d.main_desc

  from

      dbo.table_1 a

      inner join dbo.table_2 b

          on (a.id_1 = b.main_id)

      inner join dbo.table_2 c

          on (a.id_2 = c.main_id)

      inner join dbo.table_2 d

          on (a.id_3 = d.main_id)

  drop table dbo.table_1

  drop table dbo.table_2