December 26, 2011 at 9:27 am
Dear sir,
I want to fetch the employee details with different filter conditions whose schema defined below:
organizationdtls
columns : oid, orgname, level, parentid
values: 10, Unit1, 1, 0
11, Dept1, 2, 10
12, Dept2, 2, 10
13, Section1, 3, 11
Employee dtls
columns: eid, empno, orgid
values: 100, 1234, 13
Can any body help me in achieving this
when i sent values like empno : 1234 and orgid = 10 to the stored procedure..
then it should return the employee details like
eid , empno, orgid
100, 1234, 13
December 26, 2011 at 1:03 pm
Did you check Books Online (and especially its examples on the topic)
Recursive Queries Using Common Table Expressions
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 27, 2011 at 12:21 am
i have two tables organization dtls and emp dtls
organizationdtls
columns : oid, orgname, level, parentid
values: 10, Unit1, 1, 0
11, Dept1, 2, 10
12, Dept2, 2, 10
13, Section1, 3, 11
Employee dtls
columns: eid, empno, orgid
values: 100, 1234, 13
organization table has parentid which is related its own record
as example... empdtls has the orgid 13 , it means employee related to section1
when i try to fetch emp details by passing empid 1234 and org id 13 it is returning details perfetly
Question: i need to a query where it should return same details when i pass empid 1234 and orgid = 10
as section is related to unit indirectly
can u give me such query
December 27, 2011 at 2:35 am
maybe actual hierarchyid datatype can serve you better with this quest, but here's a shot using a cte:
;
WITH cteOrgHierarchy
AS (
-- Anchor member definition
SELECT oid
, orgname
, [level]
, parentid
, orgname as orgUnit
, case [level]
when 2 then orgname
else ''
end as orgDep
, 0 AS OrgLevel
FROM #organizationdtls
WHERE parentid = 0
UNION ALL
-- Recursive member definition
SELECT O.oid
, O.orgname
, O.[level]
, O.parentid
, H.orgUnit
, case O.[level]
when 2 then O.orgname
else H.orgDep
end as orgDep
, H.OrgLevel + 1
FROM #organizationdtls O
INNER JOIN cteOrgHierarchy AS H
ON H.oid = O.parentid
)
-- Statement that executes the CTE
SELECT Emp.*
, OH.orgname, OH.orgUnit, OH.orgDep
FROM cteOrgHierarchy OH
INNER JOIN #Employeedtls AS Emp
ON Emp.orgid = OH.oid
WHERE Emp.empno = 1234
and Emp.orgid = 13
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 27, 2011 at 7:29 am
thank you bro.. but it doesn't return emp details when i hit querry like this
-- Statement that executes the CTE
SELECT Emp.*
, OH.orgname, OH.orgUnit, OH.orgDep
FROM cteOrgHierarchy OH
INNER JOIN #Employeedtls AS Emp
ON Emp.orgid = OH.oid
WHERE OH.oid = 10
where in this should return same emp details as this employee indirectly related to this unit
December 27, 2011 at 7:35 am
suhailquadri (12/27/2011)
thank you bro.. but it doesn't return emp details when i hit querry like this-- Statement that executes the CTE
SELECT Emp.*
, OH.orgname, OH.orgUnit, OH.orgDep
FROM cteOrgHierarchy OH
INNER JOIN #Employeedtls AS Emp
ON Emp.orgid = OH.oid
WHERE OH.oid = 10
where in this should return same emp details as this employee indirectly related to this unit
I tested it with the data you provided.
Here's what worked for me and produced this result
eidempnoorgidorgnameorgUnitorgDep
1001234 13Section1Unit1 Dept1
create table #organizationdtls
( oid int
, orgname varchar(128)
, [level] int
, parentid int
) ;
insert into #organizationdtls values ( 10, 'Unit1', 1, 0 ) ;
insert into #organizationdtls values ( 11, 'Dept1', 2, 10 ) ;
insert into #organizationdtls values ( 12, 'Dept2', 2, 10 ) ;
insert into #organizationdtls values ( 13, 'Section1', 3, 11 ) ;
create table #Employeedtls
( eid int
, empno int
, orgid int
) ;
insert into #Employeedtls values ( 100, 1234, 13 ) ;
WITH cteOrgHierarchy
AS (
-- Anchor member definition
SELECT oid
, orgname
, [level]
, parentid
, orgname as orgUnit
, case [level]
when 2 then orgname
else ''
end as orgDep
, 0 AS OrgLevel
FROM #organizationdtls
WHERE parentid = 0
UNION ALL
-- Recursive member definition
SELECT O.oid
, O.orgname
, O.[level]
, O.parentid
, H.orgUnit
, case O.[level]
when 2 then O.orgname
else H.orgDep
end as orgDep
, H.OrgLevel + 1
FROM #organizationdtls O
INNER JOIN cteOrgHierarchy AS H
ON H.oid = O.parentid
)
-- Statement that executes the CTE
SELECT Emp.*
, OH.orgname, OH.orgUnit, OH.orgDep
FROM cteOrgHierarchy OH
INNER JOIN #Employeedtls AS Emp
ON Emp.orgid = OH.oid
WHERE Emp.empno = 1234
and Emp.orgid = 13
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 27, 2011 at 7:45 am
kindly pass parameters as oid = 10 but not 13 as oid nor even empid = 1234. It should give same results becz this employee is related to unit 10 at level0
December 27, 2011 at 7:53 am
kindly pass parameter oid = 10 eg.
SELECT Emp.*
, OH.orgname, OH.orgUnit, OH.orgDep
FROM cteOrgHierarchy OH
INNER JOIN #Employeedtls AS Emp
ON Emp.orgid = OH.oid
WHERE --Emp.empno = 1234
--and
oh.oid = 10
December 27, 2011 at 12:06 pm
If you want some real help you are going to have to help us help you. You need to post ddl (create table scripts), sample data (insert statements) and desired output based on your sample data. If you take the time to post your question clearly and provide supporting information you will receive tested in return. Take a look at the first link in my signature for best practices on posting questions in a format that is most likely to generate a favorable result.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 27, 2011 at 12:22 pm
First of all, in the future, please include the DDL and insert statements ...
create table dbo.organizationtable(
oid int primary key,
orgname varchar(20) not null,
parentid int not null);
insert into dbo.organizationtable
values
(10, 'unit1', 0),
(11, 'dept1', 10),
(12, 'dept2', 10),
(13, 'sec1', 11);
create table dbo.stafftable(
eid int primary key,
staffno int not null,
oid int not null);
insert into dbo.stafftable
values
(1, 99, 13);
Secondly, you don't need to track a level manually. The recursion you are wanting to use will take care of that itself.
Thirdly, if you want to key off a value, you need a param and you need to start with that oid rather than starting with the parent. If you start with the parent you will get the entire org every time.
declare @oid int = 10;
with cteRec as
(
select oid,
nodeLevel = 1
from dbo.organizationtable
where oid = @oid
union all
select ot.oid,
nodeLevel = cte.nodeLevel + 1
from cteRec as cte
join dbo.organizationtable as ot
on ot.parentid = cte.oid
)
select st.eid,
st.staffno,
st.oid,
cte.nodeLevel
from cteRec as cte
join dbo.stafftable st
on st.oid = cte.oid;
Lastly, recursive cte's are terrible at scaling. I would not suggest using the approach for anything but the smallest datasets. I would spend the time to learn how to use the hierarchyid datatype, which facilitates scalable performance.
December 27, 2011 at 12:23 pm
Well in this case , kindly find the table schema and data below:
create table #organizationdtls
( oid int
, orgname varchar(128)
, [level] int
, parentid int
) ;
insert into #organizationdtls values ( 10, 'Unit1', 1, 0 ) ;
insert into #organizationdtls values ( 9, 'Unit2', 1, 0 ) ;
insert into #organizationdtls values ( 11, 'Dept1', 2, 10 ) ;
insert into #organizationdtls values ( 12, 'Dept2', 2, 9 ) ;
insert into #organizationdtls values ( 13, 'Section1', 3, 11 ) ;
create table #Employeedtls
( eid int
, empno int
, orgid int
) ;
insert into #Employeedtls values ( 100, 1234, 13 ) ;
insert into #Employeedtls values ( 101, 2222, 12 ) ;
insert into #Employeedtls values ( 102, 3333, 11 ) ;
insert into #Employeedtls values ( 103, 1231, 10 ) ;
cte which i have writted is below:
with testorg as(
select oid,orgname,parentid from #organizationdtls where parentid = 0
union all
select a.oid,a.orgname,a.parentid from #organizationdtls a inner join testorg t on(a.parentid = t.oid)
)
select t.* from testorg t
Help me in achieving such output if i pass oid = 10 then it should return
empdtls:
eid staffno oid
100, 1234,13
102,3333,11
103,1231,10
Please help me !!
December 27, 2011 at 12:41 pm
I agree with the hierarchyid datatype being a better choice but you were so close it hurts...
All you really needed to do was to add an OR condition to your top query and a distinct to the final. As stated above this will not scale well with large datasets but should not be much of an issue with smaller datasets.
declare @ParentID int = 10
;with testorg as(
select oid, orgname, parentid
from #organizationdtls
where parentid = @ParentID or oid = @ParentID
union all
select a.oid, a.orgname, a.parentid
from #organizationdtls a
inner join testorg t on a.parentid = t.oid
)
select distinct t.*
from testorg t
order by t.oid
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 27, 2011 at 2:51 pm
Thanks a lot bro. It helped me a lot but has a little issue back again..
How should i get employee details when i pass the unit id as well as department id
eg. if i pass both unit id as 10 and dept id 11 then it should return employee details :
eid empno orgid
100 1234 13
102 3333 11
103 1231 10
Can you tell me how can i achieve this? or i am going in wrong track
December 27, 2011 at 3:01 pm
suhailquadri (12/27/2011)
Thanks a lot bro. It helped me a lot but has a little issue back again..How should i get employee details when i pass the unit id as well as department id
eg. if i pass both unit id as 10 and dept id 11 then it should return employee details :
eid empno orgid
100 1234 13
102 3333 11
103 1231 10
Can you tell me how can i achieve this? or i am going in wrong track
We are kind of back to providing details in your question. You don't have either a "unit id" or a "dept id" in your sample data and you desired output is the same data. I assume these are other columns in your employee table? Perhaps if you could explain what you are looking for all at once it would help. Provide all of the details of what you are looking for. Is this going to be a function? Stored proc? Best guess is you would need the query I provided in one scenario and then you would need to create a second query when you want to filter these other criteria. Probably nothing more than a where clause on the cte should do it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 27, 2011 at 3:12 pm
Well, i am creating a search form for emp details where the emp details are filtered based on four conditions
1. unit
2. department
3. Section and
4. Employee no.
As i have already discussed that unit, departmet and section are comming from the single recursive table with the column name parentid.
Q1: i want to search all employee details based on the above conditions i.e. either filter 1: unit is provided or
filter 2: unit and department is provided or
filter 3: unit and department and section is provided
In all the scenarios, it should return all the employee details matches the above filter conditions...
Kindly Can you suggest me, on this...
Sample data for the tables:
create table #organizationdtls
( oid int
, orgname varchar(128)
, [level] int
, parentid int
) ;
insert into #organizationdtls values ( 10, 'Unit1', 1, 0 ) ;
insert into #organizationdtls values ( 9, 'Unit2', 1, 0 ) ;
insert into #organizationdtls values ( 11, 'Dept1', 2, 10 ) ;
insert into #organizationdtls values ( 12, 'Dept2', 2, 9 ) ;
insert into #organizationdtls values ( 13, 'Section1', 3, 11 ) ;
create table #Employeedtls
( eid int
, empno int
, orgid int
) ;
insert into #Employeedtls values ( 100, 1234, 13 ) ;
insert into #Employeedtls values ( 101, 2222, 12 ) ;
insert into #Employeedtls values ( 102, 3333, 11 ) ;
insert into #Employeedtls values ( 103, 1231, 10 ) ;
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply