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
ADVERTISEMENT
Dec 1, 2004
Hi,I have five tables p,pm,m,i and bu. Tables 'pm' and 'm' are a branch of table 'p', and table 'bu' is another branch of table 'p'. I know how to run a query to get through the first four tables 'p','pm','m' and 'i' in the manner:
Code:
sql="SELECT p.id,p.project_name, p.fngp_item,p.project_leader,p.project_lead_center,p.project_location,m.milestone_name,i.keyed_name" & _", i.name, m.milestone_leader, m.mgr_opinion FROM PROJECT p INNER JOIN PROJECT_MILESTONE pm ON p.id = pm.source_id INNER " & _"JOIN MILESTONE m ON pm.related_id = m.id INNER JOIN [IDENTITY] i ON m.milestone_leader = i.id WHERE m.mgr_opinion='75' " & _ "order by p.project_location, p.project_lead_center"
But when i find an item where m.mgr_opinion is equal to 75 i don't know how to include in the query the item that belongs to the table 'bu', because it is in a different branch.Can anybody help me on that? Thanks
View 2 Replies
View Related
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
Apr 6, 2007
Hello. We are using asp .net and reporting services, and trying to pass a multi-value parameter into reporting services that will show data for multiple branches.
Dim paramList As New Generic.List(Of Microsoft.Reporting.WebForms.ReportParameter)
paramList.Add(New Microsoft.Reporting.WebForms.ReportParameter("BranchNumber", 1))
ReportViewer1.ServerReport.SetParameters(paramList)
pInfo = ReportViewer1.ServerReport.GetParameters()
Let me know if you have any suggestions!
Thanks.
View 4 Replies
View Related
Mar 16, 2006
I’m happy to announce the release to public CTP of the following SQL Express Family products:
SQL Server 2005 Express Editions SP1
SQL Server 2005 Express Edition with Advanced Services
SQL Server 2005 Express Edition Toolkit
SQL Server 2005 Management Studio Express (included as part of Express Advanced & Express Toolkit)
You can download and install these CTPs from the CTP download page http://www.microsoft.com/sql/ctp_sp1.mspx. Here is a brief description of each product:
SQL Server Express Edition with SP1
SQL Server 2005 Express Edition (SQL Server Express) is a powerful and reliable data management product that delivers rich features, data protection, and performance for embedded application clients, light Web applications, and local data stores. Designed for easy deployment and rapid prototyping, SQL Server Express is available at no cost, and you are free to redistribute it with applications. If you need more advanced database features, then SQL Server Express can be seamlessly upgraded to more sophisticated versions of SQL Server.
SQL Server Express Edition with Advanced Services
SQL Server 2005 Express Edition with Advanced Services is a new, free version of SQL Server Express that includes additional features for reporting and advanced text based searches. In addition to the features offered in SQL Server 2005 Express Edition, SQL Server Express with Advanced Services offers additional components that include SQL Server Management Studio Express (SSMSE), support for full-text catalogs, and support for viewing reports via report server. SQL Server Express Edition with Advanced Services also includes SP1.
SQL Server Express Edition Toolkit
SQL Server 2005 Express Edition Toolkit (SQL Server Express Toolkit) provides tools and resources to manage SQL Server Express and SQL Server Express Edition with Advanced Services. It also allows creating reports by using SQL Server 2005 Reporting Services (SSRS).
SQL Server Management Studio Express
SQL Server Management Studio Express (SSMSE) provides a graphical management tool for managing SQL Server 2005 Express Edition and SQL Server 2005 Express Edition with Advanced Services instances. SSMSE can also manage relational engine instances created by any edition of SQL Server 2005. SSMSE cannot manage Analysis Services, Integration Services, SQL Server 2005 Mobile Edition, Notification Services, Reporting Services, or SQL Server Agent.
Feel free to discuss these releases in the MSDN SQL Express Forum http://go.microsoft.com/fwlink/?LinkId=62430. (You can discuss it here too, but the MSDN Forum is the "official" forum for the CTP.)
Report any issues you find in the MSDN Product Feedback Center http://go.microsoft.com/fwlink/?LinkId=51684.
Regards,
Mike Wachal
SQL Express
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
View 2 Replies
View Related
Mar 16, 2006
I€™m happy to announce the release to public CTP of the following SQL Express Family products:
SQL Server 2005 Express Editions SP1
SQL Server 2005 Express Edition with Advanced Services
SQL Server 2005 Express Edition Toolkit
SQL Server 2005 Management Studio Express (included as part of Express Advanced & Express Toolkit)
You can download and install these CTPs from the CTP download page. Here is a brief description of each product:
SQL Server Express Edition with SP1
SQL Server 2005 Express Edition (SQL Server Express) is a powerful and reliable data management product that delivers rich features, data protection, and performance for embedded application clients, light Web applications, and local data stores. Designed for easy deployment and rapid prototyping, SQL Server Express is available at no cost, and you are free to redistribute it with applications. If you need more advanced database features, then SQL Server Express can be seamlessly upgraded to more sophisticated versions of SQL Server.
SQL Server Express Edition with Advanced Services
SQL Server 2005 Express Edition with Advanced Services is a new, free version of SQL Server Express that includes additional features for reporting and advanced text based searches. In addition to the features offered in SQL Server 2005 Express Edition, SQL Server Express with Advanced Services offers additional components that include SQL Server Management Studio Express (SSMSE), support for full-text catalogs, and support for viewing reports via report server. SQL Server Express Edition with Advanced Services also includes SP1.
SQL Server Express Edition Toolkit
SQL Server 2005 Express Edition Toolkit (SQL Server Express Toolkit) provides tools and resources to manage SQL Server Express and SQL Server Express Edition with Advanced Services. It also allows creating reports by using SQL Server 2005 Reporting Services (SSRS).
SQL Server Management Studio Express
SQL Server Management Studio Express (SSMSE) provides a graphical management tool for managing SQL Server 2005 Express Edition and SQL Server 2005 Express Edition with Advanced Services instances. SSMSE can also manage relational engine instances created by any edition of SQL Server 2005. SSMSE cannot manage Analysis Services, Integration Services, SQL Server 2005 Mobile Edition, Notification Services, Reporting Services, or SQL Server Agent.
Feel free to discuss these releases in the SQL Express Forum.
Report any issues you find in the MSDN Product Feedback Center
Regards,
Mike Wachal
SQL Express team
View 9 Replies
View Related
Nov 3, 2007
Say for example, that I have a fairly complicated record of a thingmeant for the US market. Then, a Canadian version is created based onthe US one with a few changes so I want to track the connection. It isfairly easy to make a ParentID field that would indicate the Canadianthing descended from the US thing.But, if I then make a British thing from the Canadian one (and I wantto do this because the Canadian one is a closer match to what theBritish one needs to be), it becomes more complicated... I can put theID of the Canadian one in the ParentId field of the British one. Butthen I need to run multiple queries to build the complete lineage backto the original US record.More so, if I want to allow n-number of levels to the relationshipsbetween these things, it becomes even more difficult.This type of issue has come up repeatedly in my work, so I assume itis not a new problem and that there may be a "best practice" forhandling it.Can anyone offer any advice, an answer, or point me toward a placewhere I may find the answer?Thanks in advance!
View 4 Replies
View Related
Jul 17, 2006
When trying to open the Family.MDF file in this program this error is displayed:
Database cannot be upgraded because its non-release version (587) is not supported by this version of SQL Server.
How can a current version of this file be obtained?
Thank you,
Tom
View 1 Replies
View Related
Nov 12, 2006
I am new to SQL 2005. I have setup and new maintanaince plan of making backup on to different paths but i encountered an error saying
Executing the query "BACKUP DATABASE [promis_05] TO DISK = N'E:\ERP Database\ERP Backup\Promis_05', DISK = N'\\backupsrv\ERP Backup\Promis_05' WITH NOFORMAT, INIT, NAME = N'promis_05_backup_20061111181236', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The volume on device 'E:\ERP Database\ERP Backup\Promis_05' is not part of a multiple family media set. BACKUP WITH FORMAT can be used to form a new media set.
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
We did not know what to make of this.
1) Is it bcoz i am backing up the database on 2 locations same time.
2) What is BACKUP WITH FORMAT?
3) Why won't it let me add a new file that is not part of the 'family' ?
4) How does it get to be part of the family?
Thought and ideas are highly appreciated!
View 3 Replies
View Related
Jun 24, 2014
I have a family table and would like to group all related members under the same familyID. This is a replication of existing business data, 14,000 rows. The familyID can be randomly assigned to any group, its sole purpose is to group the names:
declare @tv table (member varchar(255), relatedTo varchar(255))
insert into @tv
select 'John', 'Mary'union all
select 'Mary', 'Jessica' union all
select 'Peter', 'Albert' union all
[Code] ....
I would like my result to look like this:
familyID Name
1 John
1 Mary
1 Jessica
1 Fred
2 Peter
2 Albert
2 Nancy
3 Abby
4 Joe
4 Frank
View 3 Replies
View Related
Jul 30, 2007
I am trying to restore a 2005 backup to a 2005 database on another server. This has worked for me before. I have tried to take the backup 5 times now and each time I get the error media family incorrectly formed. Since I have successfully backed up and restored before between these same two databases I do not understand what is wrong now.
I found other questions with this error; however, they were taking a 2000 backup to a 2005 database. I am using a backup of a 2005 database to a 2005 restore.
How should I begin to debug this problem?
Thanks
View 3 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Sep 27, 2013
Here is the table - Company with fields:
CompanyID, ParentCompanyID (both integers)
Given a CompanyID - I want to get all the children for the Company.
I did similar procedures but somehow, could not get this to work.
View 4 Replies
View Related
Feb 9, 2015
I have following 4 rows in a table with field name as ID
ID
--
AA
BB
CC
DD
I require output as
ID NewColumn
-- ----------
AA AA
BB AA~BB
CC AA~BB~CC
DD AA~BB~CC~DD
View 3 Replies
View Related
