Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


correlated subquery


correlated subquery

Author
Message
ezhil-649633
ezhil-649633
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 236
Hi,

I have a table in the following structure

MgrID EmpID EmployeeName
0 1 Ezhil
1 2 Alex
1 3 Jack
2 4 Allen
3 5 Rose

I want to find the child details of the particular node if specific EmpID is given.
I already got the output through recursive function and also through CTE.

But, i want to know whether the same can be achieved through corelated subqueries. If, yes how to achieve that?

Waiting for the reply eagerly.Thanks in advance.
Mike01
Mike01
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1273 Visits: 1522
select MgrID, EmpID, EmployeeName
from emp e
where MgrID in (select EmpID from emp b
where b.EmpID = 1)



For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
ezhil-649633
ezhil-649633
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 236
Hi Mike,

By using your query i can able to get only the immediate childs of the parent.

For example when i gave 1 as input to that query, i have retrieving only the direct child of 1, in our case iam receiving 2 and 3.But i cannot able to receive all child of parent.

Expecting output:

Input - 1
Output - 2 ,4, 3, 5
Mike01
Mike01
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1273 Visits: 1522
if that's the output you're expecting, then you will need to use a CTE or recursive funtion, which you have already done.

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
ezhil-649633
ezhil-649633
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 236
Is it possible to achieve the same using 'JOINS'?
arun.sas
arun.sas
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1237 Visits: 3493
ezhil-649633 (12/4/2009)
Is it possible to achieve the same using 'JOINS'?

Hi,
try this, on inner join
create table #temp1
(
MID int,
EID int,
ENAME varchar(10)
)

insert into #temp1
select 1,1,'A'
union all
select 1,2,'AA'
union all
select 1,3,'AAA'
union all
select 1,4,'AAAA'
union all
select 2,1,'B'
union all
select 2,2,'BB'
union all
select 3,1,'C'
union all
select 3,2,'CC'
union all
select 3,3,'CCC'


select * from #temp1

declare @child_param int
set @child_param = 3

select a.* from #temp1 a
inner join
(select distinct MID from #temp1
where EID = @child_param)as b
on a.Mid= B.MID


ezhil-649633
ezhil-649633
SSC-Enthusiastic
SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)SSC-Enthusiastic (129 reputation)

Group: General Forum Members
Points: 129 Visits: 236
When i run your query against my database i have got this as output.

Input
EID = 3

Output:

MID EID E.Name
1 1 A
1 2 AA
1 3 AAA
1 4 AAAA
3 1 C
3 2 CC
3 3 CCC

But, i cannot able to under stand how this solution will fit for my suituation.Can you explain bit more to me.

Thanks
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45006 Visits: 39880
ezhil-649633 (12/3/2009)
But, i want to know whether the same can be achieved through corelated subqueries. If, yes how to achieve that?


For a finite number of levels, probably. For an unknown number of levels, no.

The recursive CTE you used has the problem of needing to recalculate the tree for whatever you're looking up every time it's used. That's probably ok if the underlying data is changing all the time. But, if the data is relatively static, then using "Nest Set Hierarchies" is probably the better way to go. The queries for downlines, uplines, and hierarchical aggregations are lightning fast because of the way they're done.

Here's a link for the Nest Set things... a GOOGLE of the subject will return more information on the subject that you can imagine... some of it is also pretty useless... :-P

http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html

If memory serves, the code isn't written for SQL Server and can stand some optimizations there. Also (if I remember correctly), the code that converts from the Adjacency Model to the Nested Set model has a small bug in it... it always leaves off the last node. I don't remember exactly what I did to fix it but I do remember the fix being fairly easy to find and repair.

As a side bar, when I do have to do such a thing as hierarchies, I tend to embed the Nested Set model into the Adjacency Model so that I have the best of both worlds.

There's a third way to do such things using a column that contains the entire "upline" path for each node. It's also quite effective especially for stable hierarchies. Here's some working example code for that method...

--=======================================================================================
-- Setup some test data... note that nothing in this section is part of the actual
-- solution.
--=======================================================================================

--===== Setup a "quiet" environment
SET NOCOUNT ON

--===== Create a table to hold some test data.
-- This is NOT part of the solution
CREATE TABLE #yourtable
(
ID INT,
ParentID INT,
Descrip VARCHAR(20)
)

--===== Populate the test table with 2 "trees" of data
INSERT INTO #yourtable
(ID,ParentID,Descrip)
SELECT 9,NULL,'County 1' UNION ALL --Note NULL, this is top node of "Tree 1"
SELECT 2,9 ,'C1 Region 1' UNION ALL
SELECT 4,9 ,'C1 Region 2' UNION ALL
SELECT 3,2 ,'C1 R1 Unit 1' UNION ALL
SELECT 5,2 ,'C1 R1 Unit 2' UNION ALL
SELECT 6,4 ,'C1 R2 Unit 1' UNION ALL
SELECT 7,NULL,'County 2' UNION ALL --Note NULL, this is top node of "Tree 2"
SELECT 8,7 ,'C2 Region 1' UNION ALL
SELECT 1,9 ,'C1 Region 3'

--=======================================================================================
-- The following code makes a Hierarchy "sister" table with strings that are used
-- to traverse various hierarchies.
--=======================================================================================
--===== Create and seed the "Hierarchy" table on the fly
SELECT ID,
ParentID,
Descrip,
Level = 0, --Top Level
HierarchyString = CAST(STR(ID,5) AS VARCHAR(8000))+' '
INTO #Hierarchy
FROM #yourtable
WHERE ParentID IS NULL

--===== Declare a local variable to keep track of the current level
DECLARE @Level INT
SET @Level = 0

--===== Create the hierarchy in the HierarchyString
WHILE @@ROWCOUNT > 0
BEGIN
SET @Level = @Level + 1

INSERT INTO #Hierarchy
(ID, ParentID, Descrip, Level, HierarchyString)
SELECT y.ID,y.ParentID,y.Descrip, @Level, h.HierarchyString + STR(y.ID,5) + ' '
FROM #yourtable y
INNER JOIN #Hierarchy h
ON y.ParentID = h.ID --Looks for parents only
AND h.Level = @Level - 1 --Looks for parents only
END

--=======================================================================================
-- Now, demo the use of the sister table
--=======================================================================================
--===== Display the entire tree with indented descriptions according to the Level
SELECT ID,
ParentID,
Level,
LEFT(REPLICATE(' ',Level*2)+descrip,30),
HierarchyString
FROM #Hierarchy
ORDER BY HierarchyString

--===== Select only the "downline" for ID 2 including ID 2
SELECT ID,
ParentID,
Level,
LEFT(REPLICATE(' ',Level*2)+descrip,30),
HierarchyString
FROM #Hierarchy
WHERE HierarchyString LIKE '% 2 %'
ORDER BY HierarchyString

drop table #Hierarchy
drop table #yourtable



Obviously, you wouldn't drop the tables once created. For relatively static tables, you'd rerun the code when rows were added, deleted, or modified.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search