Selfjoin Problem With Tree Structure

Aug 23, 2004

I have the following table
Memberid int
submemberid int

example
MemberId SubMemberId

1 2
2 3
1 4
3 5
2 6
7 8

Each member may supervise more than one member under him (whom I call submember)
and then each sub member may also supervise more than one member under him
I need to be able to select a specific member for example whose id is 1 and consequently all his sub members should be selected whose also all submembers should be selected and so
on
I do not know how to do this here is my trial:



ALTER Procedure dbo.MemberReports;1

@MemberMaskId nvarchar(8)


As
SET NOCOUNT ON
Begin
Declare @Exists Int -- Return Value

-- Select * from MembersTree where MemberId = @memId or MemberId = subMemberId


SELECT
MembersTree.memberid,
MembersTree.submemberid,
MembersTree1.memberid
FROM
MembersTree
INNER JOIN
MembersTree MembersTree1
ON MembersTree.Submemberid
= MembersTree1.memberid

WHERE
MembersTree.MemberId = @memId

If @@rowcount = 0 -- No Record returned
Select @Exists = 0
Else
Select @Exists = 1
Return @Exists

End

Please help,
Thanks

View 2 Replies


ADVERTISEMENT

SQL 2012 :: Sort Tree Members In Right (tree) Structure?

Apr 6, 2015

I got assignment, how to make it appear in the right order .

/* DROP TABLE EMP
SELECT * INTO Emp FROM (
SELECT 'A' EmpID, NULL ManID, 'Name' EmpName UNION ALL
SELECT 'MAC' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL
SELECT '1ABA' EmpID, 'MAC' ManID, 'Name____' EmpName UNION ALL
SELECT 'ABB' EmpID, '1ABA' ManID, 'Name______' EmpName UNION ALL
SELECT 'XB' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL
SELECT 'BAC' EmpID, 'XB' ManID, 'Name____' EmpName ) b
*/

[code]....

View 2 Replies View Related

MS SQL Server Tree Structure

Sep 11, 2007

Can anyone please help me? I need to create a tree structure of my company's database server. I need to include all the tables and their description in a tree structure. The output can be in any format. Is there some tool in the MSSql that will do this for me, so I don't have to write it out manualy?? Thank you very much for any ideas!!
M.

View 5 Replies View Related

Tree Structure Select Problem

Nov 6, 2005

hello i have a table with four fields : name_id, name, tree_id, level withthese data:1 name1 001 12 name2 002 13 name3 003 14 name4 001001 25 name5 001002 26 name6 001003 27 name7 001001001 3tree:1-- 4-7-5-62-3-and procedure which add nodes to tree looks tree.add(idparent,idchild,name)how to write select which returns idparent,idchild and name then i can addnodes in loop reading datareader ?

View 4 Replies View Related

Unknown Tree Structure Table Design???

Dec 11, 2006

hello friends!
Can any body guide me regarding Tree/hierarchy Multi-Referential table structure.

What i have is only for known tree structure i.e upto 3 or 4 level but if i waanna to find nth level hoe shud i design my table structure.

T.I.A

View 3 Replies View Related

Joining Two Tables To Modify A Tree Structure With More Information

Jul 30, 2007

I’m trying to create a modified catalog tree out from two tables in sql

The catalog is created from two tables.

Table 1 has the lowest level and is showing the connection with the item and the lowest ItemGroup. It also shows the connection with the MainCatalog

MainCatalogId, ItemGroupId, ItemId

Example data:

(sorry for the tabs that won't work)
MainCatalogIdItemGroupIdItemId
715063823
715073824
715093825
715093826

The catalog structure is in table 2. Here are the connections between the different


ItemGroupId, ParentId

Example data:

ItemGroupIdParentId
15061365
15071365
15091364
13641066
13651066
1066NULL

To be able to use create the tree structure and not getting the result set to big, I need it to look something like this:

ItemGroupIdItemIdLevel 2 level 3Level4
1506 3823136510667
1507 3824136510667
1509 3825136410667
1509 3826136410667

I have tried many ways, but I’m not getting the result I want.

Hope this was understandable, and that someone has an idea if this is manageable


Thanks :)

View 1 Replies View Related

Retreiving Tree-Structure Data From A Table Throug

Apr 7, 2007

Hi all
I have a Table with Following structure ( a Tree Structure )

PK Parent Level Code
--- -------- ------- ------
1 0 0 100
2 1 1 101
3 1 1 102
4 2 2 103
5 3 2 104
6 4 3 105

The same as following Tree as you can see
1__
| 2__
| 4__
| 6
| __
3__
5

I need a query to return the following Result. I think it is possible only through Nested sub-Queries But i don't know how to do that
Could any one help me.?


PK Parent Level Value First-Parent' Code 2nd-Parent's Code 3rd-Parent's Code
---- -------- ------ ------- --------------------- ---------------------- -----------------------
1 0 0 100 NULL NULL NULL
2 1 1 101 100 NULL NULL
3 1 1 102 100 NULL NULL
4 2 2 103 101 100 NULL
5 3 2 104 102 100 NULL
6 4 3 105 103 101 100

Any help greatly would be appreciated.
Kind Regards.

View 2 Replies View Related

Hierarchical (tree) Data Structure Where A Node Can Have Multiple Parents

May 9, 2008

Hi all!
I am trying to organize a hierarchical data structure into a table. I need to have the possibility to set 2 parents for some nodes. Curently I see following two options:
Example 1
id    parent_id    name-----------------------------------1     0                 Level 1 Parent A2     0                 Level 1 Parent B3     1,2              Level 2 Child
Example 2
id    parent_id    name-----------------------------------1     0                 Level 1 Parent A2     0                 Level 1 Parent B3     1                 Level 2 Child3     2                 Level 2 Child
Is any of the two examples valid database logic wise? In fact, is it possible to achieve the requirement by using only one table?
Thanks in advance,

View 4 Replies View Related

Drill Through Report Problem For Implementing Tree View Structure

Dec 21, 2007



Hai Iam new to SSRS 2005, please help me regarding below Drill Through Report Problem. Assum my problem with below example.Iam tried Hard iam not able to find the solution, Any body please help me.my real time problem is same as below functionality



If Suppose iam dispalying two columns like Country,Department,iam taking these two columns in a table, assume country column having America,south africa, individual america column having Florida state, south africa column contains capetown state, in the preview of the report iam applying drilldown to America column like + America, i need when clicking the America column i want to display Florida state under the column of Country like tree view structure

i need output like this and the same time i want to display other columns those also contain tree view structure assume other one is department column, with contains computers and sales, individualy computers contains HP, Sales contains Bikes

Country Department

+ America + Computers

---Florida -- HP

+ SothAfrica + Sales

---Capetown __Bikes



How to implement above output using drill through functionality, i tried with subreports and used all grouping formats, is possible for display output like above explain the procedure .

Thanks In advance

Jacks













View 3 Replies View Related

T-SQL (SS2K8) :: Finding Tree Structure - Show All Upward And Downward Nodes

Jun 3, 2015

I have the following table:

SELECT 'A' as Item, '1' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.1' as Version, 1 as Counter, '01-02-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.2' as Version, 2 as Counter, '01-03-2011' as CreatedDate UNION ALL
SELECT 'B' as Item, '1.2' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL

[Code] .....

I want to write a script where if a user enters the version number, then the output should show all the upward and downward nodes..e.g. if a user selects '1.2' version then following should be the output

View 3 Replies View Related

SQL Server 2008 :: DeadLock In SubQuery With Selfjoin

Oct 3, 2015

Below query. its getting more time to exec and got deadlock. So, query to avoid deadlock.

SELECT m1.Value AS InterfaceName, m1.MessageDateTime, m2.GroupId, COUNT(mError.Id) AS ErrorCount
FROM (
SELECT m1.Value, MAX(m1.MessageDateTime) as MessageDateTime FROM Message m1
WHERE m1.TypeId = 9 AND (m1.Value LIKE 'F02' )
GROUP BY m1.Value

[Code] ....

View 2 Replies View Related

Any Structure On Sql Server Like WITH ... SELECT Structure On DB2

Jul 20, 2005

Hi,I'm using DB2 UDB 7.2.Also I'm doing some tests on SQL Server 2000 for some statements touse efectively.I didn't find any solution on Sql Server about WITH ... SELECTstructure of DB2.Is there any basic structure on Sql Server like WITH ... SELECTstructure?A Sample statement for WITH ... SELECT on DB2 like belowWITHtotals (code, amount)AS (SELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY code)SELECTcode, SUM(amount)FROM totalsGROUP BY code.............................Note: 'creating temp table and using it' maybe a solution.However i need to know the definition of the result set of Unionclause. I don't want to use this way.CREATE TABLE #totals (codechar(10), amount dec(15))GOINSERT INTO #totalsSELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY codeGOSELECT code, sum(amount) FROM #totals GROUP BY codeGOAny help would be appreciatedThanks in advanceMemduh

View 3 Replies View Related

Tree

May 20, 2004

Dear all,

I would like to know how to create a single level hierachy structure in SQL.
Example, I have a single parent record in table A that may later on spawn one or child record in table B that relates back to the parent. It only needs a single level.
that means, one to many.

thanks and rgds.
Loke HC

View 1 Replies View Related

Tree

Jan 2, 2008

I have two tables.

table1 has 3 fields a , b and c. field "a" is a primary key.
table2 has 2 fields x and y. Fields x and y are nothing but the value of "a". Also, y is the child of x.
Therefore, x and y can never have same value. It means value of "a" either be child or parent.
But there is possibility that parent has no child.

Now, i wanted to write Select/Insert query for parent, b , c and child.

This tree is no a binary tree but N-Ary Tree.

Thanks,
sha


View 9 Replies View Related

Tree-walking In T-SQL

May 18, 2007

Hi,
 I'm having a major brain-failure moment here.
 Using T-SQL I want to be able to get all of the leaf nodes (e.g. nodes at the furthest end of a tree from the root) in a hierarchical relationship where the table structure is such NodeID|ParentID|NodeName.
 Basically if I had the following tree structure:
Root
    Child1
        Grandchild1
    Child2
        Grandchild2
 
I want to get all of the Grandchild nodes.  Number of levels will vary and I haven't got any kind of HasChilds column.
 I know this is possible because I remember having done it on a course years ago but I can't for the life of me figure it out on this sunny Friday afternoon.  I know it's going to involve either recursion, a while loop or cursors but my mind is currently jelly.  Can anyone help?

View 8 Replies View Related

How To Get All The Children In The MLM Tree..

Sep 10, 2007

hi. I am working on Multiline Marketing Project.I have to calculate all the childens and display the whole tree..I  have save records in tree format...But not able to calculate the all childrens.
SELECT count(*) FROM dfTree WHERE id in (SELECT id FROM dfTree WHERE lineage like '16%')
This query works properly.Problem is that it is not working in the project because  in the like I have to pass a variable.
SELECT count(*) FROM dfTree WHERE id in (SELECT id FROM dfTree WHERE lineage like '@idl%')
Please Suggest me...

View 1 Replies View Related

Need Help For Tree Functionality

Jun 2, 2008

HI,
 I am working on a Family tree portal which need tree functionality to display family members in tree structure. on click on any node the adding option should be displayed
for this i need a table and procedure to complete family tree
Thanks
@mbi

View 5 Replies View Related

Help Me Please, About Operator Tree

Jan 3, 2006

Consider the following SQL query:
SELECT ENAME,SAL
FROM EMP,ASG,PAY,PROJ
WHERE EMP.ENO=ASG.ENO
AND PAY.TITLE=EMP.TITLE
AND ASG.PNO=PROJ.PNO
AND ASG.DUR=48 AND BUDGET>200000
Give the possible operator trees:right-deep,left-deep and bush
Tank you very much!

View 2 Replies View Related

Tree Structures In SQL

Jun 11, 2006

Hi,

i'm writing a app in c# and have to store Trees in a Database.

I'm working with Datasets for the exchange between the DB and the App.

The trees have the same options like the windows folders. If u delete a node, all subnodes should be deleted too.

But something a Foreign Key from ParentID references (Id) with the delete-Rule on cascade seems not to be possible, because of multiple cascade Paths or cycles. Do i have to add some xtra constarins:

Not Possible:

create Table tree (
Id varchar Not null,
ParentId varchar Not null,
Constraint pk1 Primary Key (Id),
Constraint fk1 Foreign Key (ParentId) references tree(Id)
On Update Cascade
On delete CAscade
)


Do i have to write triggers, which delete The subnodes too and set the Update-/deleterulr on NO Action

Greetz

View 1 Replies View Related

Tree Structures...

Oct 24, 2006

Does anyone know any good links for SQL tree structures and example queries and stuff... I cant really find anything part from the standard example of emplyee, boss, salary which explains how to create the tree table...(dun dis bit) I did notice a book but I live in a little village so cant go get it till wekend?

I'm desperate, reli need to work out how too do this.....

View 14 Replies View Related

Tree Selection

Jun 13, 2008

SELECT a.Network_ID, b.Last_Name + ', ' + b.Preferred_FirstName AS full_name, c.Security_Class_Description,
d.Security_Type_Description, a.Security_Value
FROM Company_Hierarchy_Security a
JOIN V_Entity_Employee_Active b on a.Network_ID= b.Network_ID
JOIN Company_Security_Class c on a.Security_Class_Code=c.Security_Class_Code
JOIN Company_Security_Type d on a.Security_Type = d.Security_Type
inner join (select e.Budget_Center_ID + ' - ' + e.Budget_Center_Description As Budget_Center_Description,
f.Company_Name, g.Enterprise_Description, h.Business_Segment_Description,
i.Team_Description
from Company_Hierarchy_Security a.
Inner JOIN Budget_Center e on a.Security_Value = e.Budget_Center_ID
Inner JOIN Company f on a.Security_Value = f.Company_ID
Inner JOIN Enterprise g ON a.Security_Value = Cast(g.Enterprise_Number As Varchar(5))
Inner JOIN Business_Segment h on a.Security_Value = h.Business_Segment_ID
Inner JOIN Team i on a.Security_Value = i.Team_ID


Ok. I have the Security Value located in Company_Hierarchy_Security table. All those values are divided into 5 other tables that I need to join together. I found all of them separate but I have not been able to figue it out how to put it together with the rest of the querie.

Thanks for the help!!!!!

View 3 Replies View Related

Tree Selection

Jun 16, 2008

Thank You visakh16 --- The following section of my querie works but doesn't give the exact information that I need. This is what is giving me:

NetworkId Full Name Sec Class Desc Sec Type Desc SecValue
tte Test Scenario Accounting Budget Center 142- ?



SELECT a.Network_ID, b.Last_Name + ', ' + b.Preferred_FirstName AS full_name, c.Security_Class_Description,
d.Security_Type_Description, a.Security_Value
FROM Company_Hierarchy_Security a
JOIN V_Entity_Employee_Active b on a.Network_ID= b.Network_ID
JOIN Company_Security_Class c on a.Security_Class_Code=c.Security_Class_Code
JOIN Company_Security_Type d on a.Security_Type = d.Security_Type

------------------

inner join (select e.Budget_Center_ID + ' - ' + e.Budget_Center_Description As Budget_Center_Description,
f.Company_Name, g.Enterprise_Description, h.Business_Segment_Description,
i.Team_Description
from Company_Hierarchy_Security a.
Inner JOIN Budget_Center e on a.Security_Value = e.Budget_Center_ID
Inner JOIN Company f on a.Security_Value = f.Company_ID
Inner JOIN Enterprise g ON a.Security_Value = Cast(g.Enterprise_Number As Varchar(5))
Inner JOIN Business_Segment h on a.Security_Value = h.Business_Segment_ID
Inner JOIN Team i on a.Security_Value = i.Team_ID

_____________________________________________________________________

What I need is the Description that are located in 4 other different tables that matches the Security Value from my first querie.

The result should look like this...

Network Id full Name Sec Class Desc Sec Type Desc Security Value
tst , Test Example , Accounting ,Budget Center , 142-Accountig dept


Thank you very much,

View 4 Replies View Related

Organization Tree

Oct 14, 2005

Any recomendations on how to store organization trees on a database withlots of paths and branches? Any white papers out there that explain this?Thanks

View 2 Replies View Related

Tree Table

Sep 12, 2006

Hello,I have a "tree" table:Id - primary keyParrentId - (foreign key) related to IdTitle.....when I delete some record I want to delete it with all childs (cascadedeleting). I can't set cascade deleting on the same table :(. Is thereany easy way in the MSSQL 2005 to do this ? There is one idea - usingcursors + recursive functions but I think this solution is not easyand elegant.Thakns for any help and sugestions.Regards.Andy

View 4 Replies View Related

Tree View

Mar 5, 2008

Hi:

Is there a resource or an object in Reporting Services that I could make a tree view in a report file .rdl and see the result on it?

If not, is there a software of MS that a could make a component for it, and connect this component in the report file .rdl ?

View 6 Replies View Related

PLZ HELP ME WITH THE DECISION TREE!!

Apr 16, 2007

I'm having this problem.....



I wanted to use the Decision Tree to show a result..... after i configure the Mining Structures..... and set all the input.... my decision tree shows only until level 2..... i have 3 input and one PredictOnly column.....where is the other input?



Say.... i have House Owner, Marital Status, Num Cars Owned and Number Of Children(PredictOnly)



my Tree only shows All ---- > Marital Status when i input all 3 together...... the other 2 doesn't seems to show.



wat should i do?? my database in SQL Server and the other keys are all correct and deploying finely.....why is this happening.....?



i'm a newbie in this software.......so any pro here can plz help me if there's actually something that i might have missed out along the way.......



Thank you again.........

View 1 Replies View Related

Rescursive Tree

Feb 28, 2008



Alright what im trying to do is build a classic tree, i have id's like this set up in my table referencing itself in sql 2000


cmponent_prt_no parent_part_no
--------------- --------------
112837A2A L115100-1
114379A1A L115100-1
115623A1A L115100-1
203604A L115100-1
203790A L115100-1
203791A L115100-1
115623A 115623A1A
M010137 115623A1A
115623A 115623A1A
20766CR 115623A1A
DRAWINGS 20766CR
M010137 20766CR

i have tried 3 different solutions all coming to a crash at some point, if ANYONE can help me out i would appreciate it, i know the logic will be recursive, i just dont know how to implement

View 8 Replies View Related

Convert A Table Into Tree

Aug 21, 2007

Hi..I have a table  register..in this fields are username,parent id,downline ; I have to determine all the child of a particular parent.
suppose table is like this.  username   parentid        downline
                                         B                A                left
                                         C               A                 right
                                         D                B               left
                                         E                B               right....
I have to also determine the level in the tree....please help...

View 1 Replies View Related

Does Anyone Know How To Traverse A Simple Tree Using SQL?

Dec 6, 2000

Hello all!

I have a table defined as:

create table OrgTree (parent int, child int)

OrgTree has the following data:

1,2
1,3
2,4
2,5
3,6
4,7
4,8
8,9

Is there a SQL stmt that will return, given a particlar parent, all of the children (and children's children, etc.) of that parent?

So, parent value 1 returns 2,3,4,5,6,7,8,9
parent value 2 returns 4,5,7,8,9
parent value 3 returns 6
etc.

Thanks in advance for your help!!

Palmer F

View 1 Replies View Related

Database Tree Says 'No Items' ???

Oct 30, 2001

I just started having this problem. When I log into SQL2000 enterprise manager and click on my database, the main directory tree shows up. When I click on the Database tree to display all of the different databases on the SQL server, the text 'No Items' shows up. I can not get to any databases, but I am connected to the SQL server. Does anyone have a solution for this problem?????

I have tried re-inatalling and all of the service packs.

Thanks !!

View 3 Replies View Related

Family Tree Branches

Jul 26, 2004

Okie, this one has me a little stumped so bear with me an I will explain as best I can....

I have a family tree database...

The two key tables (for this problem) are Person and Child. The person table is your generic person details table and contains, PersonId, PersonFirstName, PersonLastName, PersonDOB etc. The Child table is a linking table that links a PersonId to a ChildPersonId (so both Id's are valid Id's in the person table).

Now what I want to try and achieve is a breakdown of the different branchs of the family tree. A branch is an independant, unattached section of the tree (if that makes sense). It's a grouping of people we know are related but we can't determine how they are related to another group of people in the tree.

If you look at this http://gw.geneanet.org/index.php3?b=bengos&lang=en;m=N;v=van+halewyn you will get an idea of what I mean.

I'm not sure if this is something that can be don't with a query at all the be honest... I suspect that I will have to wrap some other code around it, but I'm really not sure on what approach I should be using. Any help people could offer would be great.

View 8 Replies View Related

Hiearchical List Of Tree

Jul 8, 2007

Hi,

table(id,parent,level, name)
parent ...fk referenced table(id))
level ... depth of tree (root has 0)

How SQL for hiearchical list:

name 1
name 1.1
name 1.2
name 2.
name 2.1.
name 2.1.1
name 2.1.2
etc.

???
thanks for your help

View 12 Replies View Related

Example Code For User Tree

Sep 17, 2007

One of the users on another web site posted a question about how to associate users in a tree-like organization. That web site isn't well suited to posting code or ongoing discussions about code, so I'm going to post the example here. Feel free to discuss as you see fit.DROP TABLE LI_UserLinks
GO
DROP TABLE LI_Users
GO
DECLARE @d1DATETIME
, @d2DATETIME
, @d3DATETIME
, @d4DATETIME
, @d5DATETIME
, @d6DATETIME
, @d7DATETIME
, @d8DATETIME

SELECT @d1 = GetDate()

CREATE TABLE LI_Users (
uidINT
PRIMARY KEY (uid)
)

SELECT @d2 = GetDate()

CREATE TABLE LI_UserLinks (
uid_fromINT
CONSTRAINT XFK01LI_UserLinks FOREIGN KEY (uid_from)
REFERENCES LI_Users (uid)
, uid_toINT
CONSTRAINT SFK01LI_UserLinks FOREIGN KEY (uid_to)
REFERENCES LI_Users (uid)
CONSTRAINT XPKLI_UserLinks PRIMARY KEY (uid_from, uid_to)
)

ALTER TABLE LI_Userlinks
ADD CONSTRAINT XCK01LI_UserLinks CHECK (uid_from != uid_to)

SELECT @d3 = GetDate()

INSERT INTO LI_Users (
uid) SELECT n0 + 10 * n1 + 100 * n2 + 1000 * n3 + 10000 * n4 + 100000 * n5
FROM (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z0
CROSS JOIN (SELECT 0 AS n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z1
CROSS JOIN (SELECT 0 AS n2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z2
CROSS JOIN (SELECT 0 AS n3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z3
CROSS JOIN (SELECT 0 AS n4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z4
CROSS JOIN (SELECT 0 AS n5 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z5

SELECT @d4 = GetDate()

INSERT INTO LI_UserLinks (
uid_from, uid_to) SELECT
uid, 100 * uid + n0 + 10 * n1
FROM LI_Users
CROSS JOIN (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z0
CROSS JOIN (SELECT 0 AS n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z1
WHERE LI_Users.uid BETWEEN 0 AND 99
AND uid != 100 * uid + n0 + 10 * n1

SELECT @d5 = GetDate()

INSERT INTO LI_UserLinks (
uid_from, uid_to) SELECT
uid, 100 * uid + n0 + 10 * n1
FROM LI_Users
CROSS JOIN (SELECT 0 AS n0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z0
CROSS JOIN (SELECT 0 AS n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS z1
WHERE LI_Users.uid BETWEEN 100 AND 9999

SELECT @d6 = GetDate()

SELECT u.uid, r1.uid_to, r2.uid_to
FROM LI_Users AS u
INNER JOIN LI_UserLinks AS r1
ON (r1.uid_from = u.uid)
INNER JOIN LI_UserLinks AS r2
ON (r2.uid_from = r1.uid_to)
WHERE 1 = u.uid

SELECT @d7 = GetDate()

SELECT Count(DISTINCT u.uid), Count(DISTINCT r1.uid_to), Count(distinct r2.uid_to)
FROM LI_Users AS u
INNER JOIN LI_UserLinks AS r1
ON (r1.uid_from = u.uid)
INNER JOIN LI_UserLinks AS r2
ON (r2.uid_from = r1.uid_to)

SELECT @d8 = GetDate()

SELECT
DateDiff(ms, @d1, @d2)
, DateDiff(ms, @d2, @d3)
, DateDiff(ms, @d3, @d4)
, DateDiff(ms, @d4, @d5)
, DateDiff(ms, @d5, @d6)
, DateDiff(ms, @d6, @d7)
, DateDiff(ms, @d7, @d8)
, DateDiff(ms, @d1, @d8)-PatP

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved