locked
inner join of table RRS feed

  • Question

  • hello to all i have two tables
    one is tab1 and second is tab2
    in tab1 the dtaa is like
    sno  class     assid       throid
    1     II        4            7
    and in tab2
    sno   id     name
    1     4       pace
    2     7       turns
    Now i want to genearte output table like
    in tab1  where  assid and throid is coming  i want to take the name from tab2 according to that id..tell me how to make innner join

    kshama
    Thursday, July 14, 2011 6:34 AM

Answers

  • Perhaps something like:

    declare @dscIssue table
    ( sno integer, class varchar(5),
     assId integer, throId integer )
    insert into @dscIssue
    select 1, 'II', 4, 7
    
    declare @asthMast table
    ( sno int, id int, [name] varchar(9) )
    insert into @asthMast
    select 1, 4, 'pace' union all
    select 2, 7, 'turns'
    
    select
     a.sno,
     a.class,
     a.assId as AssoId,
     b.name as asso_Name,
     a.throId,
     c.name as thro_Name
    from @dscIssue a
    join @asthMast b
     on b.id = a.assId
    join @asthMast c
     on c.id = a.throId
    /* -------- Output: --------
    sno     class AssoId   asso_Name throId   thro_Name
    ----------- ----- ----------- --------- ----------- ---------
    1      II  4      pace   7      turns
    */
    

    .

    • Marked as answer by nikijain Friday, July 15, 2011 7:56 AM
    Thursday, July 14, 2011 11:26 AM

All replies

  • SELECT class, assid, throid, [name]
    FROM tab1 INNER JOIN tab2
    ON tab1.sno = tab2.sno
    
    


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    • Proposed as answer by Naomi NEditor Monday, July 18, 2011 1:33 AM
    Thursday, July 14, 2011 7:20 AM
  • I have done like this
    SELECT dscissue.AssoId, dscissue.ThroId ,asthmast.AsNm 
     FROM dscissue,asthmast
      WHERE dscissue.AssoId=asthmast.AsSrNo
    output is coming like this
    AssoId        ThroId         AsNm 
    19                1                pace
    30                67               yamaha
    according to AssoId  19  its coming write i.e pace   but according to throID  the data in tab2 is  'young so it should also display in Ist row'..

    kshama
    Thursday, July 14, 2011 7:49 AM
  • I have done like this
    SELECT dscissue.AssoId, dscissue.ThroId ,asthmast.AsNm 
     FROM dscissue,asthmast
      WHERE dscissue.AssoId=asthmast.AsSrNo
    output is coming like this
    AssoId        ThroId         AsNm 
    19                1                pace
    30                67               yamaha
    according to AssoId  19  its coming write i.e pace   but according to throID  the data in tab2 is  'young so it should also display in Ist row'..

    kshama


    The table and column names you're using are completely different than what you posted so which ones are correct?

    You are also using an outdated technique to join your tables.  You should be using INNER JOIN


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    Thursday, July 14, 2011 7:55 AM
  • ok sory for that now i tell u that

     

    my first table name is 'dscissue'   and second table name is 'asthmast'

     

    and in first table  i have  'AssoId'  and      'ThroId'  and according to these the name is in other table named as

              'asthmast' 

     

    now in output i wants that

    AssoId    'a'     'ThroId'    'b'

     

    where in  'a' field  the name from second table according to AssoId   should come  and

     in  'b' field  the name from second table according to 'ThroId'  should come  and

     

     

    In second table the data is like

    AsSrNo       AsNm 

    1                 young

    19               pace

     

     

    and so on.......

    Now i think u got my problem

     

             


    kshama
    Thursday, July 14, 2011 8:09 AM
  • Perhaps something like:

    declare @dscIssue table
    ( sno integer, class varchar(5),
     assId integer, throId integer )
    insert into @dscIssue
    select 1, 'II', 4, 7
    
    declare @asthMast table
    ( sno int, id int, [name] varchar(9) )
    insert into @asthMast
    select 1, 4, 'pace' union all
    select 2, 7, 'turns'
    
    select
     a.sno,
     a.class,
     a.assId as AssoId,
     b.name as asso_Name,
     a.throId,
     c.name as thro_Name
    from @dscIssue a
    join @asthMast b
     on b.id = a.assId
    join @asthMast c
     on c.id = a.throId
    /* -------- Output: --------
    sno     class AssoId   asso_Name throId   thro_Name
    ----------- ----- ----------- --------- ----------- ---------
    1      II  4      pace   7      turns
    */
    

    .

    • Marked as answer by nikijain Friday, July 15, 2011 7:56 AM
    Thursday, July 14, 2011 11:26 AM
  • Thanks very much ..it works great
    kshama
    Friday, July 15, 2011 7:57 AM