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