Simple join query question

  • Hi all,

    I have 2 tables, one is employee master table (T1) which contains employeid and jobtitle, another table (T2) is pay information table which contains employid and paycode and payrate.

    If the employee has jobtitle as '030' in T1, then his paycode=1135 in T2.

    How to join these 2 tables to get employid, jobtitle and payrate for each employee?

    Thank you.

  • select T2.employid, jobtitle,payrate

    from employee T1 inner join pay T2

    on (T1.employeid=T2.employid)

    where T1.jobtile='030' and T2.paycode=1135

    -Krishnan

  • sorry that i didn't make it clear, but here is the example:

    T1:

    employid   Jobtitle

    123456789   030

    999999999   036

    T2

    employid    Paycode  Payrate

    123456789   1135    10

    123456789   7000    50.69

    999999999   1200    50000

    999999999   3200    146

    There is no lookup table for jobtitle vs paycode, HR just know 030=1135 and 036=1200.

    So I need the result:

    employid   Jobtitle  Payrate

    123456789   030    10

    999999999   036    50000

  • >>HR just know

    It is difficult, if not impossible, to code a solution for a business problem where "people just know stuff".

    You need to dig up the business rules/requirements before writing a single line of code.

     

  • If you put the information that they "just know" into the database then you will be able to run your query

  • The db needed to designed after knowing all the business requirements...

    Anywayz,If you need a lookup then build a lookup table based on the existing data.... and add the new or missing entries..   or simply write a small UDF to get the required data from the employid like dbo.GetPayCodeFromEmployId or dbo.GetJobtitleFromEmployid 

     

    select  distinct jobtitle,paycode

    from employee T1 inner join pay T2

    on (T1.employeid=T2.employid)

     

    -Krishnan

  • You need another table:

    JobTitle, Paycode.

    Record known relations between those entities into this table and use it in your join.

    _____________
    Code for TallyGenerator

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

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