Problem in Join
-
Hi, I have three table 1. EmpMst 2. RefDef 3. RefValue Empmst contains EmpCode, EmpName, DesigCode,DeptCode RefDef contains Definitions like department, designation etc. RefValue stores def_code from RefDef table and also stores uniqueid of value and description. Means Designation and Department both are stored in on table under same column. now i want to get result like EmpCode, EmpName, Designation, Department. Problem is how to get unique records.
kiranbanker
-
Hi, I have three table 1. EmpMst 2. RefDef 3. RefValue Empmst contains EmpCode, EmpName, DesigCode,DeptCode RefDef contains Definitions like department, designation etc. RefValue stores def_code from RefDef table and also stores uniqueid of value and description. Means Designation and Department both are stored in on table under same column. now i want to get result like EmpCode, EmpName, Designation, Department. Problem is how to get unique records.
kiranbanker
You would want a query like
select empmst.empcode, empmst.empname, refvalue_1.description as designation, ref_value_2.description as department
from empmst inner join refvalue refvalue_1
on empmst.desigcode = refvalue_1.value
inner join refvalue refvalue_2
on empmst.deptcode = refvalue_2.valueThis assumes that the values in field RefValue.value are unique to the table. If they are not, you would have to filter the records in refvalue_1 and refvalue_2 by the appropriate def_code. I'm not sure why you are getting duplicate records though.
Paul Marfleet