Get All Children Of A Parent If Present Or Parent Itself?
Nov 21, 2012
I have the below query which in turn goes in the where clause
Code:
SELECT CASE WHEN EXISTS (SELECT child.id FROM InspectionType parent,
InspectionType child WHERE parent.id = child.parentId AND
parent.code = 'INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129)
THEN (SELECT child.id FROM InspectionType parent, InspectionType child
WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129)
ELSE (SELECT id FROM InspectionType WHERE code = 'INSPECTION_TYPE_SAFETY_LIFE' AND zoneId = 10129)
END
I am getting below error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I tried to solve this problem with If and else but it is not going well with in-clause
Code:
select * from InspectionType where id in (select IF EXISTS (SELECT child.id FROM InspectionType parent,
InspectionType child WHERE parent.id = child.parentId AND
parent.code = 'INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129)
BEGIN (SELECT child.id FROM InspectionType parent, InspectionType child
WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129)
END
ELSE (SELECT id FROM InspectionType WHERE code = 'INSPECTION_TYPE_SAFETY_LIFE' AND zoneId = 10129) )
I am using select * from InspectionType where id in() for illustation only. The sub-query results will go another query
Code:
SELECT child.id FROM InspectionType parent, InspectionType child
WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129
This returns more than one row.I know why is the error message but need to achieve this functionality.
I have made an SSRS report using the recursive parent functionality to show a hierarchical tree of values. The problem I have is that some children have more than one parent, but because (in order to use the recursive parent nicely) I need to group the results by Id, I only see distinct entries. This means that I only see each child once, even if it "should" appear in multiple locations in the report (under each of its parents).
I am creating a SSRS report connecting to a cube , Which has geography as Dimension as Hirerarchy ( the Hirerachy consists of Region, Subregion) and a measure , Now this geography dimension is taken as Parameter
when i apply filters i am getting the filter based on Parameter selection , But how can i get the all children under the same parent
As i need to create a graph based on report parameter (ie i should get all the children under the Select parameter parent)
to be more specfic consider this
Region SubRegion
Asia India
Asia Pakistan
Asia Japan
Australia Melborne
Suppose my report parameter is India , I should get all the values under Asia(Parent of india)
I am working on a project where I have a page that will have a parent record (Product) and then 1 or more children (options available for the product, user enters text to define) displayed in a table/gridview. There is a relationship defined in the database between the product and options table). My question is how can I allow the user to add the product info and then within the same page also add the options and only then save it all? The options will added to a table. Thanks for any help
Include children and exclude children in a single hierarchy in parent child dimension in mdx
*12-parent **20-parent - 9-parent --250-child1 --210-child2 --240-child3 aggregation of 12-parent only aggregation of 20-parent only aggregation of 9 with children
I have a master table and i need to import the rows into the parent and child table.
Master table name is Flatfile_Inventory Parent Table name is INVENTORY Child Tables name are INVENTORY_AMOUNT,INVENTORY_DETAILS,INVENTORY_VEHICLE, Error details will be goes to LOG_INVENTORY_ERROR
I have 4 duplicate rows in the Flatfile_Inventory which i have already inserted in the Parent and child table.
Again when i run the query using stored procedure,its tells that all the 4 rows are duplicate and will move to the Log_Inventory_Error.
I need is if i have the duplicate rows in the flatfile_Inventory when i start inserting into the parent and child table the already inserted row have the unique ID i must identify it and delete that row in the both parent and chlid table.And latest row must get inserted into the Parent and child table from Flatfile_Inventory.
Given the sample data and query below, I would like to know if it is possible to have the outcome be a single row, with the ChildTypeId, c.StartDate, c.EndDate being contained in the parent row. So, the outcome I'm hoping for based on the data below for ParentId = 1 would be:
1 2015-01-01 2015-12-31 AA 2015-01-01 2015-03-31 BB 2016-01-01 2016-03-31 CC 2017-01-01 2017-03-31 DD 2017-01-01 2017-03-31
declare @parent table (Id int not null primary key, StartDate date, EndDate date) declare @child table (Id int not null primary key, ParentId int not null, ChildTypeId char(2) not null, StartDate date, EndDate date) insert @parent select 1, '1/1/2015', '12/31/2015' insert @child select 1, 1, 'AA', '1/1/2015', '3/31/2015'
Guys, could anyone tell me if MSSQL Server 7 has 'on delete cascade' option when creating a foreign key constraint or something similar to it. I'd really like MSSQL to remove all dependent records (child records) automatically from one table when I'm deleting a parent record from another record. I know that I can do it via trigger, but the FK constraint should be removed or disabled. I would really appreciate your help. Thank you very much.
i get a error message like this: Table RENTAL_ITEM in RENTALB23 does not have a matching parent key.
this is the sql: CREATE TABLE RENTALB23/RENTAL_ITEM (RENTALITEM# INT PRIMARY KEY, RENTALTYPE CHAR(3), TITLE CHAR(30), CATEGORY CHAR(15), DUEDATE DATE, FOREIGN KEY(RENTALTYPE) REFERENCES RENTALB23/RENTAL_CHARGE(RENTALTYPE), FOREIGN KEY(CATEGORY) REFERENCES RENTALB23/RENTAL_CHARGE(CATEGORY))
this is the sql for the rental_charge table: CREATE TABLE RENTALB23/RENTAL_CHARGE (RENTALTYPE CHAR(3), CATEGORY CHAR(15), CHARGE DECIMAL(5,2), PRIMARY KEY(RENTALTYPE, CATEGORY))
whats the best way to delete a parent record? for now what i am doing is to have a special delete page (means not DetailsView/GridView) just to delete parent records so user can see what child records will be deleted too. is the a better way?my code below: The T-SQL Stored ProcedureALTER PROCEDURE dbo.DeleteInventory ( @InventoryID int ) AS DELETE FROM Booking WHERE InventoryID = @InventoryID
DELETE FROM Inventory WHERE InventoryID = @InventoryID The code in the Code Behind to execute when a button(delete) click Try Dim temp As Integer = SqlDataSource4.Delete If temp > 0 Then lblDeleteDtatus.ForeColor = Drawing.Color.Blue lblDeleteDtatus.Text = temp & " Records/Rows Deleted." Else lblDeleteDtatus.ForeColor = Drawing.Color.Orange lblDeleteDtatus.Text = "No records Deleted!" End If Catch ex As Exception lblDeleteDtatus.ForeColor = Drawing.Color.Red lblDeleteDtatus.Text = "An Error Occured.<br />" & ex.Message.ToString End Try any better methods anyone wants to share?
hi, i have two tables i want the identity value of the parent table to be inserted into the chile table here is my code,but i don't know why it isn't working ! protected void Button1_Click(object sender, EventArgs e) { string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; string pcontent = TextBox1.Text; string data = TextBox2.Text; addtopic(pcontent,connectionString); addfile(data, connectionString); } public void addtopic(string subject,string connstring) { using (SqlConnection connection = new SqlConnection(connstring)) { SqlCommand command = new SqlCommand("INSERT INTO parent" + "(content)" + "Values(@content)", connection); command.Parameters.Add("@content", SqlDbType.Text).Value = subject; connection.Open(); command.ExecuteNonQuery(); } } public void addchild(string name, string connstring) { using (SqlConnection connection = new SqlConnection(connstring)) {Guid id = Guid.NewGuid(); SqlCommand commandd = new SqlCommand("INSERT INTO child" + "(parentid,data,uniqueid)" + "Values(@@IDENTITY,@data,@uid)", connection); commandd.Parameters.Add("@data", SqlDbType.NVarChar, 50).Value = name; commandd.Parameters.Add("@uid", SqlDbType.UniqueIdentifier).Value = id;
I'm building a page that will display link categories with the links under each one. I have that part working however there is a code issue that I cant quite figure out related to the SQL query. I want to be able to have the category only display if the 'linkcolumn' is set to 1 in the database. (so I can later separate which ones show in each column of the page. However, I get an error if I try to make my 'select' statement have a 'where' in it. I think it does this because it ends up not showing all the records but I'm not sure why. Exception Details: System.ArgumentException: This constraint cannot be enabled as not all values have corresponding parent values. My codebehind file: using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient; using System.Data.Sql;using System.Data.SqlTypes; public partial class Modules_NCG_NCGLinks : System.Web.UI.UserControl {public void Page_Load(object sender, EventArgs e) { //Create the connection and DataAdapter for the Category table.SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["mystringname"].ConnectionString); SqlDataAdapter cmd1 = new SqlDataAdapter("select * from NCG_Links_Category Where LinkCategory = 'Test'",cnn); //Create and fill the DataSet.DataSet ds = new DataSet(); cmd1.Fill(ds, "NCG_Links_Category"); //Create a second DataAdapter for the Links table.SqlDataAdapter cmd2 = new SqlDataAdapter("select * from NCG_Links",cnn); cmd2.Fill(ds,"NCG_Links"); //Create the relation bewtween the Category and Links tables.ds.Relations.Add("myrelation", ds.Tables["NCG_Links_Category"].Columns["LinkCategory"],ds.Tables["NCG_Links"].Columns["LinkCategory"]); //Bind the Category table to the parent Repeater control, and call DataBind.parentRepeater.DataSource = ds.Tables["NCG_Links_Category"]; Page.DataBind(); //Close the connection. cnn.Close(); }private void Page_Init(object sender, EventArgs e) { InitializeComponent(); }private void InitializeComponent() { this.Load += new System.EventHandler(this.Page_Load); } }
I have a parent/child relationship in a relational database broken out like this: Table Name: categories[category_id] int (primary_key NOT NULL),[category_name] varchar(50),[parent_fk] int The parent references the category_id in the same table to create the parent/child relationships. I can get all the bottom level categories by doing this: select category_id, category, parent_fk from categories where category_id not in ( select parent_fk from categories) Each bottom-level category has a count attached to it. The problem I have is getting the counts rolled up for each parent of the bottom level. A parent could/will have multiple bottom-level categories (and counts). My sql is a little weak, could you help me out? I can utilize everying in SQL 2000 (stored proc, UDF, anything). Thanks!
i have a dimension named 'sdoc' which has 2 members 1) symptom word( first hierarchical level) and 2) doc id (second hierarchical level)
more than one 'symptom word' can have the same 'doc id'. i want to know which all symptom words have a particular doc id (say 10.) if i give [10].parent then it returns only the first parent whereas it actually has more than one parent. how to retrieve all the parents.
I need to update the price of one row based on another. In this case it takes the price from a sku which has a value in the "ShadowOf" field. Shadow Of is a parent sku. It takes the parent sku price, adds 2% and applies it to the child sku. It's all working well except I'm not sure how to handle it when a parent has more than one child. It updates the first & sets the rest to null.Enclosed is the sample data + query.
I do not want to rollback child procedure when i am rollbacking parent procedure after execution of child procedure. in child procedure i am committing transaction and in parent rollbacking transaction. Is it possible. Is there any solution to avoid rollbacking child procedure when i rollbacking parent procedure.
I want to find all the child of a node in a tree . A child can have multiple parent i.e 2 can be place under multiple parent . The folling is the data:
This structure requires complicated queries (recursive call) to find out all the child of a root node, so I have added another field for the root id. Is this a good relational database design ? kindly suggest.
In our database we have a list of devices in a "Device" Table, eachhaving one or more IP's located in the "IP" Table linked through aforein key on the DeviceID Column.I would like to retrieve this information as SuchDeviceID IpAddress1 10.0.0.1, 10.0.0.2, 10.0.0.32 ...345etc.Is it possible to do that without using cursors? Through a query?
I'm working on a report in which I need to group data in a drill down table. Specifically, I'm tracking statistics on how many users are running a specific build. So, right now, my group header displays the results from:
SELECT DISTINCT BuildName as [FriendlyName] FROM DataSource WHERE ProductID = 9 //<- 9 being just for example
Beneath each of these entries, I want the content to reflect the number of occurances of a specified build name, something like:
- Alpha
19 - Beta
144
So,
SELECT COUNT(BuildName) as [Count] FROM DataSource WHERE ProductID = 9 AND BuildName = (The name of the build, as specified by the header row that contains us)
Is there a way to access that last bit of data in VS 2005 Reporting, or do I really have to write a separate count query for Alpha, Beta, etc...
This is my first exposure to both SQL and Reporting. I'm overwhelmed. Please help.
Hello all, I'm having a real hard time trying to figure this one out. I'm trying to create a sql query that selects both the parent name and it's children, but it's got to loop through all the record sets to populate a drop down as an end result.
I think I thought this out correctly: I have 2 tables
category relationship
tbl category cat_id //auto int cat_name // varchar
relationship r_id // auto int parent_id // int child_id // int
both the parent_id and child_id are associated with the cat_id in my category table I could have 1cars // this is parent 2 audi 3 bmw 4 chevy
Table data example
r_id parent_id child_id **************************** 1 1 15 2 1 16 3 1 17 4 2 55 5 2 56 etc... I want to select both the parent cat_name from category and also select the child cat_name where the parent_id = #
I can do it manaully like this select cat_name, cat_id, parent_id , child_id from category, relationships where child_id = cat_id and parent_id = 1
what is the best way to loop through all the parent ids to find child category? Could this be done in a stored procedure?
I am having problems creating a trigger in SQL Server? I have 2 tables (parent and child) with one to many relationship. When I save a record, one row gets inserted in the parent and one to many gets inserted in the child. The trigger is on the parent table and it is trying to select the number of new records just inserted in the child table that meets a certain criteria. Since the transaction hasn't been committed I can not select the number of records from the child. Does anyone know how to handle this? My manager insists this be done in a trigger. Thanks, James
William Smith, (555)555-5555, 123 Main Street, Susie, Peter, Bill Jr, Fred Jason Jones, (666)666-6666, 54332 South Ave, Brian, Steven Kay McPeak, (777)777-7777, 9876 Division NW, Kathy, Sally, Karen, Deb, Becky, Kendra, Ann, Edward
with an unknown number of children for each parent.
Then I would like to be able to query against this view with something like this:
SELECT * FROM FamilyView Where Child2 = 'Peter'
I have no idea how to write the SQL for this View. Is it possible? Is this possible without using a cursor?
Below is my sample data of my table named "Groups"
Code: with Groups as ( select 1 as GroupId,'Oracle' as GroupName,0 as IdParentGroup union all select 2 as GroupId,'Microsoft' as GroupName,0 as IdParentGroup union all select 3 as GroupId,'IBM' as GroupName,0 as IdParentGroup union all select 4 as GroupId,'SunMicrosystem' as GroupName,1 as IdParentGroup union all select 5 as GroupId,'peoplesoft' as GroupName,1 as IdParentGroup union all select 6 as GroupId,'mysql' as GroupName,1 as IdParentGroup union all select 7 as GroupId,'Nokia' as GroupName,2 as IdParentGroup union all select 8 as GroupId,'EShop' as GroupName,2 as IdParentGroup union all select 9 as GroupId,'Meiosys' as GroupName,3 as IdParentGroup union all select 10 as GroupId,'UrbanCode' as GroupName,3 as IdParentGroup ) select * from groups;
Expected result:
Code: with ExpectedResult as ( select 'Oracle' as GroupName,'SunMicrosystem' as SubGroup union all select '' as GroupName,'peoplesoft' as SubGroup union all select '' as GroupName,'mysql' as SubGroup union all select 'Microsoft' as GroupName,'Nokia' as SubGroup union all select '' as GroupName,'EShop' as SubGroup union all select 'IBM' as GroupName,'Meiosys' as SubGroup union all select '' as GroupName,'UrbanCode' as SubGroup ) select * from ExpectedResult;
some sample query to how to achieve this parent-child has the same table.
I have hundreds of rows in Table A, each with an Organization ID, Organization Name, and Parent ID.
There's also an Organization table which contains the Organization mappings (this also has an Organization ID, Organization Name, Parent ID). - The parent ID is basically an Organization ID, referring to the Parent Organization (and this is recursive, so each Parent can have another Parent etc.)
The problem I have is in Table A. I need to create new columns showing the parent hierarchy starting from the Organization Name in the table.
This step is quite easy, simply using a JOIN with the Organization table ON Parent ID works pretty well.
The complication comes here:
-Every Organization in Table A is the lowest level in its hierarchy (i.e. it doesn't have any children). - Every Organization in Table A has a varying amount of parents (i.e. some might have 1 parent who then has another parent, or some might have 1 parent as the BOSS (top most level))
I need to organise the Table so that each organization can have its parents listed beside (one in each column), and should show NULL if it doesn't have a parent at a certain level. - This bit is difficult to explain, so I've attached an image
So essentially, regardless of how many parents an organization has, the top most parent (BOSS) should always appear under the same column. (image explains this)...
I'm an entre level junior programmer. My question is kind of confusing but I'll try to put it as simple as I can.
First we have a main table called "job1". This table consists the order information. The file_id is the unique id and the primary key for this table. This table also pertains other information such as customer data (max limit 5), job data etc. This table is actively (non-stop) used throughout the day.
We have a non-interactive process which will take customers information from the main table and insert into the child table table "jobcust". Jobcust would have file_id, cust, cust_type. For example, if Job1 table had fiel_id=100 and cust1="Tom" and Cust2="David", now Jobcust will have two records file_id, cust1 and file_id,cust2. The main problem is the child table needs to be updated right away and our non-interactive process is good at doing that.. but it is causing a major DATA LATENCY. I would like to ask you all, if you know any better way of doing this without any process.. like in the back end with a trigger/procedure or something like that.
I am importing data from a paradox table and trying to clean it up. I have this query that finds all the child records that are not in the parent table.
Select MemberID FROM memtype AS a WHERE NOT EXISTS (SELECT * FROM members AS b WHERE a.MemberID IN (b.MemberID));
Now I'm trying to delete all those child records instead of just selecting them so I tried...
Delete MemberID FROM memtype AS a WHERE NOT EXISTS (SELECT * FROM members AS b WHERE a.MemberID IN (b.MemberID));
I have a parts table which has partid (GUID) column and parentpartId (GUID) column. Need to copy the records to the same table with new GUIDs for partids. How to do that? cursor or temp tables?
