Post reply

Join problem

  • August 24, 2009 at 5:36 am

    #214030

    Hi,

    I have a table called

    Candidate which have

    CREATE TABLE [Candidate] (

    [ID] [int] NULL ,

    [jobcode] [varchar] (50) not NULL ,

    [fullame] [varchar] (100) NULL

    )

    GO

    its qualification table

    CREATE TABLE [qualification] (

    [ID] [int] NOT NULL ,

    [jobcode] [varchar] (50) not null,

    [exam] [varchar] (50) NULL ,

    [spec] [varchar] (50) NULL ,

    [passingyear] [varchar] (50) NULL ,

    [institution] [varchar] (50) NULL ,

    [cgpa] [varchar] (50) NULL ,

    [type] [varchar] (50) NULL

    )

    GO

    Records in canddidate table will be,

    id jobcode FullName

    1000 SSO John

    1001 SSO Smith

    Records in qualification table

    id jobcode exam spec passingyear institution cgpa type

    1000 SSO B.com accounts 2000 St. XYZ 72 15

    1000 SSO M.com accounts 2002 St. XYZ 70 17

    1001 SSO Bsc pcm 2000 St.XYZ 89 15

    here 15 =graduate and 17 =post graduate.

    How Do I join candidates and qualification using alias table such that,

    I get two records

    Output column should be

    id jobcode fullname exam spec cgpa pgexam pgspec pgcgpa

    1000 SSO John B.com accounts 72 M.com accounts 70

    1001 SSO Smith Bsc pcm 89

    It should be exactly like this.

    If exam type is 17 then it should show null under alias column.

    Thanks

    [font="Verdana"]Regards
    Kumar Harsh[/font]

    :unsure:

  • August 24, 2009 at 6:05 am

    #1043559

    Hi

    didnt we walk down a very similar road a few weeks ago?

    http://www.sqlservercentral.com/Forums/Topic751567-338-1.aspx

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • August 24, 2009 at 10:59 pm

    #1043989

    Hi,

    Then my problem didn't solve.

    I hope this time my problem is clear.

    I want records in those particualr format,so that I can use it in my application with ease.

    Also ,it'll clear my mental block.

    Thanks

    [font="Verdana"]Regards
    Kumar Harsh[/font]

    :unsure:

  • August 25, 2009 at 10:57 pm

    #1044671

    Hi,

    Finally problem solved.

    First create temp table1(create column name accordingly) and insert record of candidate table and qulaification of table where candidate.id=qualification.id and candidate.jobcode=qualification.jobcode and qualification.type='15'

    again join candidates table and qulaification of table where candidate.id=qualification.id and candidate.jobcode=qualification.jobcode and qualification.type='17'

    And in temp table1(create diiffrent column name) and insert thses records.

    Finally,

    select * from table1

    drop table1

    Thanks.

    [font="Verdana"]Regards
    Kumar Harsh[/font]

    :unsure:

  • August 25, 2009 at 11:03 pm

    #1044674

    Hi,

    Finally problem solved.

    First create temp table1(create column name accordingly) and insert record of candidate table and qulaification of table where candidate.id=qualification.id and candidate.jobcode=qualification.jobcode and qualification.type='15'

    again join candidates table and qulaification of table where candidate.id=qualification.id and candidate.jobcode=qualification.jobcode and qualification.type='17'

    And in temp table1(create diiffrent column name) and insert thses records.

    Finally,

    select * from table1

    drop table1

    Thanks.

    [font="Verdana"]Regards
    Kumar Harsh[/font]

    :unsure:

  • August 25, 2009 at 11:05 pm

    #1044675

    Hi,

    Finally problem solved.

    First create temp table1(create column name accordingly) and insert record of candidate table and qulaification of table where candidate.id=qualification.id and candidate.jobcode=qualification.jobcode and qualification.type='15'

    again join candidates table and qulaification of table where candidate.id=qualification.id and candidate.jobcode=qualification.jobcode and qualification.type='17'

    And in temp table1(create diiffrent column name) and insert thses records.

    Finally,

    select * from table1

    drop table1

    Thanks.

    [font="Verdana"]Regards
    Kumar Harsh[/font]

    :unsure:

  • August 25, 2009 at 11:19 pm

    #1044678

    Hi,

    Finally problem solved.

    First create temp table1(create column name accordingly) and insert record of candidate table and qulaification of table where candidate.id=qualification.id and candidate.jobcode=qualification.jobcode and qualification.type='15'

    again join candidates table and qulaification of table where candidate.id=qualification.id and candidate.jobcode=qualification.jobcode and qualification.type='17'

    And in temp table1(create diiffrent column name) and insert thses records.

    Finally,

    select * from table1

    drop table1

    Thanks.

    [font="Verdana"]Regards
    Kumar Harsh[/font]

    :unsure:

  • August 25, 2009 at 11:21 pm

    #1044679

    Hi,

    Finally problem solved.

    First create temp table1(create column name accordingly) and insert record of candidate table and qulaification of table where candidate.id=qualification.id and candidate.jobcode=qualification.jobcode and qualification.type='15'

    again join candidates table and qulaification of table where candidate.id=qualification.id and candidate.jobcode=qualification.jobcode and qualification.type='17'

    And in temp table1(create diiffrent column name) and insert thses records.

    Finally,

    select * from table1

    drop table1

    Thanks.

    [font="Verdana"]Regards
    Kumar Harsh[/font]

    :unsure:

Viewing 8 posts - 1 through 7 (of 7 total)

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