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.?
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 */
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,
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.
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
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 ?
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 .
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
I have an app that needs to retrieve the list of tables in an SQL database. I see that there is a way to do this with the OLEdb provider (GetOleDbSchemaTable() ), but I can't find a sibling method in SQL. IS there one/ Any Ideas?
We have SQL Enterprise Manager (8.0). Is there any way to Copy a Table from one database to another with only the Structure (design) - not all the Data? I can't find any option in the Import Data Wizard that only copies the Table structure. Any help is appreciated. Thanks
In order to export data to a 3rd party provider, I build five separate tables to store the data. Every table has a different layout, except for the first four columns. They are record type, SSN, employee id and another id number.
Basically, I have to sort that data by SSN then by record type. Each employee will have multiple records.
However, that data will need to be "merged" into one table to be exported.
I have created a table that defines the first four columns, but then has one large "filler" field that will contain the rest of the data. How can I copy data from five different tables with five different layouts into one table?
I have an application with MSAccess as front end and SQLServer as backend. have quite a bit of tables. i wanted to write a stored procedure which exports a SQL Server table (both Structure & Data) to a new Access MDB file. i know with the use of DTS its possible but i need to code it down. i need to perform this at runtime. so can anybody help. Its urgent.
I have a large fact table spread across tens of partitions (appx. 1TB each). I found that the business does not need much of the columns in the table. So, as an optimization action, I decided to get rid of these un-needed columns.What is the efficient way to achieve this? Can I simply drop these columns from the table, or use a new table with the reduced structure?
Hello. I want to ask about the possibility of copying both a tablestructure and it's contents from aSQL server table to a table within MS access. The problem cannot besolve with a permanent table structure at the target location.The names of the columns are essentially data with the application andso are subject to change. I am targeting a solution using SQL QueryManager.The approach I have tried (with failure) isSELECT *INTO <linkedserver table>FROM <local table>This should create and copy. However, I am not sure if this isachievable with this approach.Refer to the dialogue;-------------------------------------------------------USE MASTERGOEXEC sp_addlinkedserver@SERVER = 'Freddie',@PROVIDER = 'Microsoft.Jet.OLEDB.4.0',@SRVPRODUCT = 'OLE DB Provider for Jet',@DATASRC = 'C: empHMIS_Recipe.mdb'-- I am not sure if this is requiredEXEC sp_addlinkedsrvlogin 'Freddie', false, 'sa', 'Admin', NULLSELECT * FROM Freddie...FRED -- This is OKSELECT * INTO #Temp FROM Freddie...FRED -- This is OK-- This fails - Refer errorSELECT * INTO Freddie.FRED65from #tempServer: Msg 2760, Level 16, State 1, Line 1Specified owner name 'Freddie' either does not exist or you do not havepermission to use it.-- This also fails and I thought reflected the above select with naming- Refer errorSELECT * INTO Freddie...FRED65from #tempServer: Msg 117, Level 15, State 1, Line 2The object name 'Freddie...' contains more than the maximum number ofprefixes. The maximum is 2.EXEC sp_dropserver 'Freddie',@droplogins = 'droplogins'------------------------------------------------------------Thank you.Regards JC...
Hello, can anyone tell me if it is possible to conditionally join tables? If so, how could it be done?
What I would like to do is create a query similar to the one below but include the rows in the Asset table where the corresponding Alert field is NULL.
Assets AS A ON AL.IndustryId = A.IndustryId AND AL.RegionId = A.RegionId AND AL.RevenueId = A.RevenueId WHERE AlertId = 1
The output I am after would be the first row in the Assets table. But since the RevenueId column is NULL, I get nothing. The only time the above query will work is if all three Id columns are populated. That is not always going to be the case. Please don't suggest changing table structures or adding data. That is not an option.
Could someone tell me how to get the data from all tables of the database in the form of insert script? We are moving our databse from SQL Server 2000 to SQL Server 2005. The scripts for the Database, Tables, Views , Procedures, Functions have been obtained and it is only the data that is remaining. Some are small tables with 5 to 6 columns but there are some with 50 odd columns. A friend of mine told me about a procedure that returns a dataset with INSERT statements by passing a table name as a parameter. Such procedure would be of great help.
I have been trying to write a query that will allow me to search through text fields. This is a problem because SQL doesn't let me use any functions on TEXT datatypes.
When I was using access I did it like this:
where upper(searchtext) like ('%SEARCHSTRING%')
However UPPER doesn't work on text fields (I want the search to be case insensitive)
I tried this: where patindex(searchtext, '%SEARCHSTRING%') <> 0
but that is not case-insensitive... Help me SQL gurus, you are my only hope
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
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...
Hi all,I am rather new to database design and modelling concepts in generaland was hoping for some advice on a problem I am trying to solve. Ihave designed a piece of software that creates a tree with pluggablenodes. Each node class can have 0 to n distinct classes plugged intoit to define the type for that node.For example, a node plugged with a 'customer' class and an 'engineer'class would indicate that this node in the tree is an engineer who isalso a customer. We could also have a 'owner', 'engineer' etc.I now want to persist this tree in an SQL Server 2000 Database. I havechosen to implement the nested set model, and have thought about thefollowing table design:table NODE_TABLE:lft INTEGERrft INTEGERpropsID INTEGERtable PROPERTIES_TABLE:propsID INTEGERtableName VARCHARtable CUSTOMER_TABLE:propsID INTEGERfirstname CHARlastname CHARtable ENGINEER_TABLE:propsID INTEGERnum_completed_projects INTEGERdegree CHARschool CHARtable OWNER_TABLE:propsID INTEGERcompanyName CHARSo, given the above example - I would have a NODE_TABLE that links to 2entries in PROPERTIES_TABLE. One entry would link to an entry in theCUSTOMER_TABLE, the other to an entry in ENGINEER_TABLE.Are there any more efficient solutions to this problem? As i said, Iam very new to DB design and would welcome any feedback or suggestionsof how else I might model my pluggable tree in a Database. Thank you,Bob Yohan
And I always know the root ID from the first record on "table" dummy (generated with a common table expression), in this case it's ID 1, but from here, how to process this for any level of depth ?
I'm playing with CTE and just want to expand my skills and ask how you would build this tree structure to fill that [Tree] column for table like in sample below:
/* CREATE TABLE #T1 (child_id INT, parent_id INT, tree VARCHAR(MAX))
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database which is configured and set on the hosting web server. Right now for managing my sql server 2005 database, i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database, it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors. Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still the problem persist.
we have a table in our ERP database and we copy data from this table into another "stage" table on a nightly basis. is there a way to dynamically alter the schema of the stage table when the source table's structure is changed? in other words, if a new column is added to the source table, i would like to add the column to the stage table during the nightly refresh.
