locked
Merge 2 table display in Gridview RRS feed

  • Question

  • User-1736042568 posted

    Hi everyone, I have one problem. I have 2 table

    1.resumetopr(resumetoprID, UserIC, PrNo)

    2. personnelrequisition(PrId, PrNo, PrType, JobTitle, JobGrade, MinQualification)

    in table resumetopr, I want to count no of UserIC belong to PrNo in personnelrequisition table.

    I want to display all data from table personnelrequisition in gridview. Added 1 more column-(no of userIC belong to each PrNo)

    Anyone know how to do?

    Friday, October 14, 2011 3:25 AM

Answers

  • User3866881 posted

    Hello:)

    I want to display all data from table personnelrequisition in gridview. Added 1 more column-(no of userIC belong to each PrNo)

    To link the two tables together, you should have a primary key. And it's strongly recommanded that every table should have its ow primary key as an identifier symbol.

    So my suggestions are:

    1) Please add a primary key to ResumeToPr (called "Id", identitied by 1 from 1)

    2) And then link the two tables together:

    select personnelrequisition.*,temptable.TotalNum
    From personnelrequisition
    Inner join (select PrNo,Count(PrNo)As TotalNumber From ResumeToPr Group by PrNo) as temptable
    on personnelrequisition.PrNo = temptable.PrNo

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 15, 2011 8:45 PM

All replies

  • User1983249378 posted

    You need a simple query for your grid ie.

    SElect personnelrequisition.PrId, personnelrequisition.PrNo, personnelrequisition.PrType, personnelrequisition.JobTitle, personnelrequisition.JobGrade, personnelrequisition.MinQualification , Count(resumetopr.UserIC) as [TotalUsers]

    From personnelrequisition InnerJoin resumetopr On personnelrequisition.PrNo=resumetopr.PrNo

    Group By personnelrequisition.PrId, personnelrequisition.PrNo, personnelrequisition.PrType, personnelrequisition.JobTitle, personnelrequisition.JobGrade, personnelrequisition.MinQualification

    Friday, October 14, 2011 3:42 AM
  • User-1736042568 posted

    Sample data: ResumeToPr table(many to many)

    |UserIC             | PrNo |

    ----------------------------------

    |800525146199 | A001 |

    |800525146199 | A002 |

    |830316075402 | A001 |

    |801221075402 | A001 |

    |801221075401 | A002 |

    Sample data: personnelrequisition

    |PrId                                                       | PrNo | PrType | JobTitle | JobGrade | MinQualification |

    ------------------------------------------------------------------------------------------------------------------------------

    |f829311a-7c57-4604-9768-5a856b1736bd | A001 | Internal | Engineer    | E4            | Degree |

    |f829311a-7c57-4604-9768-5a856b1736bc | A002 | Internal | Engineer    | E4            | Degree |

    |8f829311a-7c57-4604-9768-5a856b1736be | A003 | Internal | Engineer  | E4            | Degree |

    |f829311a-7c57-4604-9768-5a856b1736bf | A004 | Internal | Engineer    | E4            | Degree |

     

    Result

    PrNo  |  No Of User |

    --------------------------------

    A001 |     5    |

    A002 |     3    |

    A003 |      0   |

    Cannot do inner join because resumetopr table have no primary key? Do u have idea how to get output like this

    umairaslam22?? Thanks

    Friday, October 14, 2011 4:48 AM
  • User3866881 posted

    Hello:)

    I want to display all data from table personnelrequisition in gridview. Added 1 more column-(no of userIC belong to each PrNo)

    To link the two tables together, you should have a primary key. And it's strongly recommanded that every table should have its ow primary key as an identifier symbol.

    So my suggestions are:

    1) Please add a primary key to ResumeToPr (called "Id", identitied by 1 from 1)

    2) And then link the two tables together:

    select personnelrequisition.*,temptable.TotalNum
    From personnelrequisition
    Inner join (select PrNo,Count(PrNo)As TotalNumber From ResumeToPr Group by PrNo) as temptable
    on personnelrequisition.PrNo = temptable.PrNo

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 15, 2011 8:45 PM