sam2929
asked on
getting supervisor name
Hi,
I have table d_job
job_id position_nbr report_to effdt
1 100 200 1997-01-01
2 100 200 2012-01-01
3 200 300 1997-01-01
4 200 300 2012-01-01
then d_position
position_nbr empl_id
100 123
200 234
then `d_emp,
empl_id name
123 tom
234 jim
i want to get supervisor name
select a.position_nbr,c.name as supervisorname
from d_job a
left outer join d_position b
on a.position_nbr=b.position_
left outer join d_position c
on b.empl_id=c.empl_id
want to get result like below
position_nbr report_to emplid supervisor_name
100 200 234 jim
Thanks
I have table d_job
job_id position_nbr report_to effdt
1 100 200 1997-01-01
2 100 200 2012-01-01
3 200 300 1997-01-01
4 200 300 2012-01-01
then d_position
position_nbr empl_id
100 123
200 234
then `d_emp,
empl_id name
123 tom
234 jim
i want to get supervisor name
select a.position_nbr,c.name as supervisorname
from d_job a
left outer join d_position b
on a.position_nbr=b.position_
left outer join d_position c
on b.empl_id=c.empl_id
want to get result like below
position_nbr report_to emplid supervisor_name
100 200 234 jim
Thanks
johnsone
Why the outer joins? Are you saying it is possible for a person to not have a position or a supervisor?
Your sample data doesn't represent your expected result. Please check that.
If you are somehow supposed to take into account effdt, you are not showing that.
This should give you what you are looking for, assuming effdt has no bearing.
If you are somehow supposed to take into account effdt, you are not showing that.
This should give you what you are looking for, assuming effdt has no bearing.
SELECT a.position_nbr,
a.report_to,
b.empl_id,
d.name supervisor_name
FROM d_job a
join d_position b
ON a.position_nbr = b.position_nbr
join d_position c
ON a.position_nbr = c.position_nbr
join d_emp d
ON c.empl_id = d.empl_id; ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My bad :
empl_id is linked from job to emp so c to a connection was wrong
select a.position_nbr,c.name as supervisorname
from d_job a
left outer join d_position b
on a.position_nbr=b.position_
left outer join d_emp c
on a.empl_id=c.empl_id
empl_id is linked from job to emp so c to a connection was wrong
select a.position_nbr,c.name as supervisorname
from d_job a
left outer join d_position b
on a.position_nbr=b.position_
left outer join d_emp c
on a.empl_id=c.empl_id
D_JOB does not have a EMPL_ID field. At least not according to your sample data.
ASKER
My bad we need to linl position and empl via job so data looks like below.
d_JOB
job_id position_nbr effdt empl_id
1 100 1997-01-01 123
2 100 2012-01-01 123
3 200 1997-01-01 234
4 200 2012-01-01 234
d_position
position_id position_nbr report_to
1 100 200
4 200 300
d_emp
empl_id name
123 tom
234 jim
d_JOB
job_id position_nbr effdt empl_id
1 100 1997-01-01 123
2 100 2012-01-01 123
3 200 1997-01-01 234
4 200 2012-01-01 234
d_position
position_id position_nbr report_to
1 100 200
4 200 300
d_emp
empl_id name
123 tom
234 jim
Now you need to explain the D_POSITION table. How do I get from a REPORT_TO of 200 to an actual name? There is nothing that corresponds to a name with a key of 200 (or 300 for that matter).
Assuming that the columns names are correct and sample data is wrong, this should work:
Since you still haven't answered the question of EFFDT, this is still a total guess if that is supposed to be accounted for:
There is also still no explanation on why the need for outer joins either.
Assuming that the columns names are correct and sample data is wrong, this should work:
SELECT a.position_nbr,
b.report_to,
a.empl_id emplid,
c.name supervisor_name
FROM d_job a
join d_position b
ON a.position_nbr = b.position_nbr
join d_emp c
ON b.report_to = c.empl_id; Since you still haven't answered the question of EFFDT, this is still a total guess if that is supposed to be accounted for:
SELECT a.position_nbr,
b.report_to,
a.empl_id emplid,
c.name supervisor_name
FROM (SELECT position_nbr,
empl_id
FROM (SELECT position_nbr,
empl_id,
Row_number()
over (
PARTITION BY position_nbr
ORDER BY effdt DESC) rn
FROM d_job)
WHERE rn = 1) a
join d_position b
ON a.position_nbr = b.position_nbr
join d_emp c
ON b.report_to = c.empl_id; There is also still no explanation on why the need for outer joins either.
ASKER
This join is not correct.
ON b.report_to = c.empl_id;
ON b.report_to = c.empl_id;
As I said, I guessed because you don't have columns to join on. If you can give working sample data with all the correct columns, maybe we can get somewhere. Or if you can explain how to get to where you need to. The query is there, you just need to supply all the right joins and column names.
It looks to me like your query can be as simple as this (if you just want one row returned):
select a.position_nbr, a.report_to, b.emplid, c.name as supervisorname
from d_job a, d_position b, d_emp c
where b.position_nbr = a.position_nbr
and c.empl_id = b.empl_id
and a.position_nbr = 100;
If you want more rows to be returned, you will have to remove or change the last line of this query.
Don't use outer joins if you don't need them. In this simple example, I doubt if you need outer joins. If you do, that indicates that the data entry was sloppy or not validated.
select a.position_nbr, a.report_to, b.emplid, c.name as supervisorname
from d_job a, d_position b, d_emp c
where b.position_nbr = a.position_nbr
and c.empl_id = b.empl_id
and a.position_nbr = 100;
If you want more rows to be returned, you will have to remove or change the last line of this query.
Don't use outer joins if you don't need them. In this simple example, I doubt if you need outer joins. If you do, that indicates that the data entry was sloppy or not validated.