TREE TRAVERSING , ADDING UP THE RESULT SET

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 2000 Forums

Transact-SQL (2000)

TREE TRAVERSING , ADDING UP THE RESULT SET

Author

Topic

ferozkhan
Starting Member

5 Posts

Posted - 2007-03-01 : 19:51:19

Hi,
I have a strange problem with sybase query . Let me explain in detail. I have a query that has a subquery in the WHERE clause. The subquery is basiacally a query to traverse a tree syntax is

SELECT DISTINCT CHILD.PERSONNEL_ID FROM PERSONNEL CHILD, PERSONNEL PARENT
WHERE PARENT.PERSONNEL_ID IN (7198) AND CHILD.LFT BETWEEN PARENT.LFT AND PARENT.RGT AND PARENT.LFT <> 0 AND PARENT.RGT <> 0

the root personnel id is 7041 .

Here is the query :
SELECT COUNT(*) FROM LABOR_COMMITED LC, ACCOUNTS AC, PERSONNEL P1, PERSONNEL P
WHERE LC.ACCOUNT_ID = AC.ACCOUNT_ID
AND LC.PERSONNEL_ID = P.PERSONNEL_ID
AND AC.ACCOUNT_MANAGER = P1.PERSONNEL_ID
AND P1.PERSONNEL_ID IN (SELECT DISTINCT CHILD.PERSONNEL_ID FROM PERSONNEL CHILD, PERSONNEL PARENT
WHERE PARENT.PERSONNEL_ID IN (7198,7041) AND CHILD.LFT BETWEEN PARENT.LFT AND PARENT.RGT AND PARENT.LFT <> 0 AND PARENT.RGT <> 0)

Its counting the total number of rows.

Now If I pass 7198 as the parent.personnel_id (WHERE PARENT.PERSONNEL_ID IN (7198)) then the count(*) returns 9055 records , which is correct .

If I pass 7041 as the parent.personnel_id (WHERE PARENT.PERSONNEL_ID IN (7041)) , then the count(*) returns 28710 records which is also correct .

But if pass , 7198 , 7041 together(WHERE PARENT.PERSONNEL_ID IN (7198,7041)) then it adds up the number of records
i.e. 28710 + 9055 = 37765
and returns me 37765 .
Any idea , why its doing so ?
Here is when I run these query , what I got ( this was just to test)

SELECT count(DISTINCT CHILD.PERSONNEL_ID) FROM PERSONNEL CHILD, PERSONNEL PARENT
WHERE PARENT.PERSONNEL_ID IN (7198) AND CHILD.LFT BETWEEN PARENT.LFT AND PARENT.RGT AND PARENT.LFT <> 0 AND PARENT.RGT <> 0

returns 87 records

SELECT count(DISTINCT CHILD.PERSONNEL_ID) FROM PERSONNEL CHILD, PERSONNEL PARENT
WHERE PARENT.PERSONNEL_ID IN (7041) AND CHILD.LFT BETWEEN PARENT.LFT AND PARENT.RGT AND PARENT.LFT <> 0 AND PARENT.RGT <> 0

returns 1621 records

SELECT count(DISTINCT CHILD.PERSONNEL_ID) FROM PERSONNEL CHILD, PERSONNEL PARENT
WHERE PARENT.PERSONNEL_ID IN (7198,7041) AND CHILD.LFT BETWEEN PARENT.LFT AND PARENT.RGT AND PARENT.LFT <> 0 AND PARENT.RGT <> 0

returns 1621 records

So, here its not adding up 1621 + 87 = 1708 ( rather its returning 1621).

thanks in advance . Oh yes, this is not in SQL Server, this is in Sybase ASE 12.5 .
-feroz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources