column alias

• blnbmv

  SSC Eights!

  Points: 900

  More actions

  August 7, 2008 at 11:19 am

  #195619

  Hi members,

  I have a query below like this

  select t1.grandid, t1.parentid, t1.id, t1.classcode,convert(int,'2') as protectcode

  from table1 t1 inner join table2

  on t1.grandid=t2.grandid

  and t1.parentid=t2.grandid

  protectcode=t2.protectioncode

  I actually have 4 columns that is grandid,parentid,id,classcode in table1 and I created a separate column protectcode in select statement.

  My basic question is protectcode is the column which is not there in table1 but there is protectioncode in table2 and i want to apply join based on column which is not there in table1

  how should i solve this problem Should I take temp table with 4 columns and last column as protectcode and then apply join with table2 or what??

  Thanks,

  sainath

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  August 7, 2008 at 11:25 am

  #854648

  I'm not sure why you do the convert(int, '2') instead of just 2, but if you are setting a constant you can just use the constant in your join. Like this:

  [font="Courier New"]SELECT

     t1.grandid,

     t1.parentid,

     t1.id,

     t1.classcode,

     CONVERT(INT,'2') AS protectcode

  FROM

     table1 t1 INNER JOIN

     table2 ON

         t1.grandid=t2.grandid AND

         t1.parentid=t2.grandid AND

         CONVERT(INT,2)=t2.protectioncode[/font]

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• blnbmv

  SSC Eights!

  Points: 900

  More actions

  August 7, 2008 at 1:56 pm

  #854743

  Hi jack,

  Thanks a lot.

  I issue is resolved.

  Thanks,

  sainath