Please solve this problem. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Please solve this problem.

Author

Topic

whitebird
Starting Member

12 Posts

Posted - 2008-03-12 : 07:21:59

hi,
i have employee table like this.
emp_id,emp_name,emp_designationpid, emp_salaryRangePid
1 'rose' 1 1
2 'rose1' 2 1
3 'rose2' 3 1

emp_desgination table
designation_id , name
1 'manager'
2 'employee'

Managers_salaryRange Table
salary id , salary-start, salary-end
1 1000 2000

Emp_salaryRange Table
salary id, salary-start, salary-end
1 500 1000

I want a single query:
if the employee role is manager i want to inner join with managers_salaryRange Table.
if the employee role is employee then i want to inner join with
emp_salaryrange table.

please give me single sql query to solve my problem

Raghu sunkara.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-12 : 07:26:23

Show us what you have tried so far and what problem you are facing.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

whitebird
Starting Member

12 Posts

Posted - 2008-03-12 : 07:33:31

hi Harsh Athalye,
i need a query which i explained above. actually, there is two differenct tables which explains the salary range for manager and employee roles. so, i need to inner join based on the roles with corresponding salary range tables.
could you please tell me is it possible to get in a single query?

Raghu sunkara.

ranganath
Posting Yak Master

209 Posts

Posted - 2008-03-12 : 07:42:11

Hi,

you split the table in to two parts for manager, Employee for roleswise
select * into #Manager
from emp_desgination table where name = 'Manager'
select * into #Emplyee
from emp_desgination table where name = 'Emplyee'

then join with these Tables

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 07:44:30

I don't think that qualifies as a "single query"...

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 07:52:32

[code]-- Prepare sample data, not part of solution
DECLARE @Employee TABLE (EmpID INT, EmpName VARCHAR(20), DesignationID INT, RangeID INT)

INSERT @Employee
SELECT 1, 'rose', 1, 1 UNION ALL
SELECT 2, 'rose1', 2, 1 UNION ALL
SELECT 3, 'rose2', 3, 1

DECLARE @Designation TABLE (DesignationID INT, Name VARCHAR(20))

INSERT @Designation
SELECT 1, 'manager' UNION ALL
SELECT 2, 'employee'

DECLARE @ManSal TABLE (RangeID INT, SalStart INT, SalEnd INT)

INSERT @ManSal
SELECT 1, 1000, 2000

DECLARE @EmpSal TABLE (RangeID INT, SalStart INT, SalEnd INT)

INSERT @EmpSal
SELECT 1, 500, 1000

-- Solution starts here
SELECT e.EmpID,
e.EmpName,
d.Name AS RoleName,
COALESCE(ms.SalStart, es.SalStart) AS SalStart,
COALESCE(ms.SalEnd, es.SalEnd) AS SalEnd
FROM @Employee AS e
LEFT JOIN @Designation AS d ON d.DesignationID = e.DesignationID
LEFT JOIN @ManSal AS ms ON ms.RangeID = e.RangeID
AND d.Name = 'Manager'
LEFT JOIN @EmpSal AS es ON es.RangeID = e.RangeID
AND d.Name = 'Employee'[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources