Terminology Question - Set Of Related Records In Multiple Tables
Jul 20, 2005
Terminology question:
Is there a term for a set of records related directly or indirectly by key
value in several tables? For example, a single invoice record and its line
item records -or- a single customer, the customer's orders, the order lines
for those orders, the customer's invoices, and the invoice lines for those
invoices.
I'm thinking the term might be graph, but I'm not at all certain of this.
Thanks,
Steve J
View 17 Replies
ADVERTISEMENT
Jan 30, 2014
I am trying to delete the records in 2 related tables. The 'child' table has a field called [SETA],
I want to delete all the records in this table that contain the same info, as well all the fields in the parent table that is related to this table. They share the [ID] field as key. This is my code:
Code:
DELETE FROM Student a full outer JOIN Qualification b on a.[ID] =b.[ID] WHERE b.[SETA] = @SETA
View 4 Replies
View Related
Sep 24, 2007
Hi,
I have 3 tables:
Employees with the fields:idEmployee and employeeName
Roles with the fields:idRole and roleName.
An employee can have one or many roles.
I created the table EmployeeRoles with the fields: id,idEmployee,idRole.
idEmployee and idRole are foreign keys.
I want to insert a new employee into Employees table, but I have to insert idEmployee field into EmployeeRoles table.
How can I do this?
Thanks
View 6 Replies
View Related
Jun 2, 2005
I have a problem there must be an answer to, but I cannot find it anywhere on Google.I have a SQL INSERT statement in an ASP.Net page that inserts into 3 tables in one transaction.The problem is that 2 of the 3 tables are children of the main table, and I need to get the Parent table's Primary Key [which of course has not been inserted yet] to insert into the Child tables.How do I do this?THanks.Doug
View 1 Replies
View Related
Jun 18, 2007
Hey guys up until now i've only inserted data into a single table. Now I have a form that collects information over a span of three forms. Each form has a table related to it and these three tables are related to each other.
What I want to know is:
1)How do you go abouts inserting data into multiple related tables that have constraints on them?
2)Would you use a stored procedure in an instance like this?
3)At what stage would you execute the sql queries. I assume you do this once you have collected all the required information as opposed to: Enter info into form1, submit form1 data to database... enter info into form2, submit form2 data into database etc
Any help would be greatly appreciated!
Say for instance I have three related tables.
table1
------
tbl1_id
tbl1_data1
tbl1_data2
table2
------
tbl2_id
tbl2_data1
tbl2_data2
table3
------
tbl3_id
tbl3_data1
tbl3_data2
table1 has a one-to-many relationship with table2
table3 has a one-to-one relationship with table2
View 3 Replies
View Related
Nov 4, 2004
i want to select all the user tables within the database and then all the records with in each table.
plz tell me one query to do this.
ex: suppose x and y are user tables and x contain 10 records and y contains 20 records . i want a query which displays all 30 records together.
View 1 Replies
View Related
Jan 20, 2006
What is the simplist/correct way to delete multiple records from multiple tables. Any help appreciated. Thanks! (Yes, I'm totally new to this.)
delete dbo.tblcase
where dbo.tblcase.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaseclient
where dbo.tblcaseclient.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaseinformation
where dbo.tblcaseinformation.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaselawyer
where dbo.tblcaselawyer.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
delete dbo.tblcaseprosecutor
where dbo.tblcaseprosecutor.case_id in ('299760', '299757', '299739', '299732', '299902',
'299901', '299897', '299894', '299873', '299872', '299870', '299865', '299860',
'299858', '299854', '299846', '299838', '299834', '299821', '299813', '299803')
View 1 Replies
View Related
Aug 29, 2006
I have searched in length and cant seem to find a specific answer.I have a tmptable to hold user "shoppingcart" ( internal supplies)What i want is to take when the user clicks order to take that table pull the records with that user and populate the order and order details tablesThe order table has a PK of orderID and the orderdetails has a FK of orderIDI know how to insert to the main table, but dont know how to populate the details at the same timeI have this.Insert into supplyordersselect requestor from tmpordercart where requestor = &name so how do i also take from the tmpordercart the itemno and quanity and put them into the orderdetails so that it links back to order table?
View 1 Replies
View Related
Apr 2, 2004
I need to update two tables. I have created a view and am using the code in the attached file to insert into the two tables.
The page loads without errors, but I get this message that the view is not updatable because the modification affects multiple base tables.
I thought this was the purpose of views?
Does anyone have any suggestions? I am using Dreamweaver MX and SQL Server.
Thanks!
N
View 11 Replies
View Related
Apr 10, 2007
i wants to insert fields of one form in more than one table using stored procedure with insert query,but i gets error regarding foreign key
View 3 Replies
View Related
Oct 31, 2007
Hi, I have had this problem for a while and have not been able solve it.
What im looking at doing is looping thru my patient table and trying to organise the patients in to there admission sequence, so when patient "A" comes in and is treated at my hospital and is discharged and admitted to another Hospital within one day then patient "A" will get a code of 1 being there first admission.
then if patient "A" is admitted again but there admission date is greater than one day they get a code of 2 being for there second admission but will need to loop thru table looking for other admissions and discharges.
The table name is Adm_disc_Match_tbl
Basically what i have 4 fields.
Index_key = which is the patient common link (text)
ur_episode = this wil change for each Hospital (text)
Admission_datetime = patient admission date and time (datetime)
Discharge_datetime = patient discharge date and time (datetime)
example of data
Code: ( text )
Index_key,ur_episode,Admission_datetime,discharge_ datetime
HERBERT-7/1929,513884-1686900,4/07/2006 10:58,17/07/2006 13:37
HERBERT-7/1929,C023092-1698859,17/07/2006 13:20,24/07/2006 0:30
ELSIE-5/1916,G148445-1720874,8/08/2006 11:00,30/08/2006 10:00
STANISLAWA-3/1918 ,G119981-1720045,8/08/2006 13:01,22/08/2006 12:13
FREDA-11/1925,183772-1998910,27/03/2007 9:53,3/04/2007 11:06
FREDA-11/1925,G147858-2007408,3/04/2007 10:49,26/04/2007 12:39
FREDA-11/1925,183772-2037727,28/04/2007 17:05,9/05/2007 11:41
FREDA-11/1925,G147858-2052082,9/05/2007 12:00,25/05/2007 11:17
If anyone could help it would be much appreciated.
View 6 Replies
View Related
Mar 17, 2014
Using SSE 2012 64-bit.I need to insert records from multiple Access Tables into 1 Table in SSE and ensure no duplicates are inserted.This is executing, but is very slow, is there a faster way?
Code:
INSERT INTO dbTarget.dbo.tblTarget
(All fields)
SELECT
(All Fields)
FROM dbSource.dbo.tblSource
WHERE RecordID NOT IN (SELECT RecordID FROM dbTarget.dbo.tblTarget)
View 6 Replies
View Related
Jul 13, 2007
I'm new to relational database concepts and designs, but what i've learned so far has been helpful. I now know how to select certain records from multiple tables using joins, etc. Now I need info on how to do complete deletes. I've tried reading articles on cascading deletes, but the people writing them are so verbose that they are confusing to understand for a beginner. I hope someone could help me with this problem.
I have sql server 2005. I use visual studio 2005. In the database I've created the following tables(with their column names):
Table 1: Classes --Columns: ClassID, ClassName
Table 2: Roster--Columns: ClassID, StudentID, Student Name
Table 3: Assignments--Columns: ClassID, AssignmentID, AssignmentName
Table 4: Scores--StudentID, AssignmentID, Score
What I can't seem to figure out is how can I delete a class (ClassID) from Classes and as a result of this one deletion, delete all students in the Roster table associated with that class, delete all assignments associated with that class, delete all scores associated with all assignments associated with that class in one DELETE sql statement.
What I tried to do in sql server management studio is set the ClassID in Classes as a primary key, then set foreign keys to the other three tables. However, also set AssignmentID in Table 4 as a foreign key to Table 3.
The stored procedure I created was
DELETE FROM Classes WHERE ClassID=@classid
I thought, since I established ClassID as a primary key in Classes, that by deleting it, it would also delete all other rows in the foreign tables that have the same value in their ClassID columns. But I get errors when I run the query. The error said:
The DELETE statement conflicted with the REFERENCE constraint "FK_Roster_Classes1". The conflict occurred in database "database", table "dbo.Roster", column 'ClassID'.
The statement has been terminated.
What are reference constraints? What are they talking about? Plus is the query correct? If not, how would I go about solving my problem. Would I have to do joins while deleting?
I thought I was doing a cascade delete. The articles I read kept insisting that cascade deletes are deletes where if you delete a record from a parent table, then the rows in the child table will also be deleted, but I get the error.
Did I approach this right? If not, please show me how, and please, please explain it like I'm a four year old.
Further, is there something else I need to do besides assigning primary keys and foreign keys?
View 6 Replies
View Related
Aug 30, 2015
This is a bit lengthy, but lets say we have three tables
a) tblSaleStatementCustomer
b) tblCreditors
c) tblReceiptDue
which shows records like below
Table 1 - tblSaleStatementCustomer
ID  CustomerName   VoucherType   Outbound   Inbound   CustomerType
----------------------------------------------------------------------------------------------
1   ABC                Sales         10000        0          Dealer
2   MNC               Sales          9000        0          Dealer
3   MNC               Sales          4000        0          Dealer
Table 2 - Â tblCreditors
ID  Name   OpeningBalance
----------------------------------------------------------------------------------------------
1 Â Â ABC Â Â Â Â Â 20000 Â
2 Â Â MNC Â Â Â Â 15000Â
3 Â Â XBM Â Â Â Â 18000
4 Â Â XYZ Â Â Â Â Â 12000
View 2 Replies
View Related
Aug 18, 2007
Data_Staging:
Unique_id
Gender
Ethnicity
Race
MCP_key
Admission_Dt
Discharge_Date
Enrollment_key
Reason
Disability
Income
Employment
I need to load the data from this table to three different tables all have foreign key relationship
Registration Table:
Registration_key ( Indetity) -PK
Unique_id
Gender
Ethnicity
Race
Episode:
Episode_Key(Identity)- PK
Registration_key (FK)
MCP_key
Admission_Dt
Discharge_Date
Assessment Table:
Assessment_Key(Identity) €“ PK
Registraion_Key(FK)
Episode_Key(FK)
Enrollment_key
Reason
Disability
Income
Employment
View 1 Replies
View Related
Jun 4, 2008
I would like to know if it's possible to return a single record by joining the tables below. [Persons]
PersonID [int] | PageViewed [int]
=============== =================
1 10
2 5
3 2
4 12
[PersonNames] - PersonID JOINS Persons.PersonID
PersonID [int] | NameID [int] | PersonName [nvarchar] | PopularVotes [int]
=============== ============== ======================= ===================
1 1 Samantha Brown 5
1 2 Samantha Green 10
2 3 Richard T 10
3 4 Riko T 0
4 5 Sammie H 0
[AltNames] - backup for searches caused by common spelling mistakes
AltNameID [int] | AltNames [nvarchar]
================ =============================
1 Sam, Samantha, Sammie, Sammy
2 Riko, Rico
[PersonAllNames] - JOINS [PersonNames.NameID] ON [AltNames.AltNameID]
NameID [int] | AltNameID [int]
============= ================
1 1
4 1
3 2
This is ideally what I'd like to have returned: PersonID | PageViewed | MostPopularName | NameSearch
========= ============ ================= =================
1 10 Samantha Green Samantha Brown, Samantha Green, Sam, Samantha, Sammie, Sammy
2 5 Richard T Richard T
3 2 Riko T Riko T, Riko, Rico
4 12 Sammie H Sammie H, Sam, Samantha, Sammie, Sammy
[MostPopularName] is [PersonNames.PopularVotes DESC].[NameSearch] combines all records from [PersonNames.PersonName] and [AltNames.AltNames].
The purpose for this is that I'd like to cache the results table so that all searches can just perform a lookup against the NameSearch field.
Any help would be greatly appreciated.
Thanks, Pete.
View 4 Replies
View Related
Jul 14, 2014
I have tried joining several tables and the result displays duplicate rows of virtually every line/row. I have tried using distinct but this didn't work. I know it could because there's several columns from some of the tables named the same.
select purchaseorders.traderid,
suppliers.name
stockbatches.partid,
allpartmaster.partdesc,
allpartmaster.prodgroup,
[Code]....
View 2 Replies
View Related
Jul 1, 2015
i have this query in a proc
declare @bu_id INT,
@CurCaptureDate DATETIME,
@user_id INT,
@col_name VARCHAR(100),
@sort_order VARCHAR(4),
@CityPair_ID INT=NULL,
[code]....
where @reasons and @departure_code can be multiple.
View 2 Replies
View Related
Sep 1, 2015
I want to create a view to get records from multiple tables. I have a UserID in all the tables. When I pass UserID to view it should get records from multiple tables. I have a table
UserInfo with as data as
UserID=1, FName = John,
LName=Abraham and Industry = 2. I have a
Industry table with data as
ID=1 and Name= Sports,
ID =2 and Name= Film.
When I query view where UserID=1 it should return record as
FName =John,
LName =Â Abraham and
Industry=Â Film
How to write query?
View 2 Replies
View Related
Mar 6, 2008
I have received some data out of a relational database that is incomplete and I need to find where the holes are. Essentially, I have three tables. One table has a primary key of PID. The other two tables have PID as a foreign key. Each table should have at least one instance of every available PID.
I need to find out which ones are in the second and third table that do not show up in the first one,
which ones are in the first and third but not in the second,
and which ones are in the first and second but not in the third.
I've come up with quite a few ways of working it but they all involve multiple union statements (or dumping to temp tables) that are joining back to the original tables and then unioning and sorting the results. It just seems like there should be a clean elegant way to do this.
Here is an example:
create table TBL1(PID int, info1 varchar(10) )
Create table TBL2(TID int,PID int)
Create table TBL3(XID int,PID int)
insert into TBL1
select '1','Someone' union all
select '2','Will ' union all
select '4','Have' union all
select '7','An' union all
select '8','Answer' union all
select '9','ForMe'
insert into TBL2
select '1','1' union all
select '2','1' union all
select '3','8' union all
select '4','2' union all
select '5','3' union all
select '6','3' union all
select '7','5' union all
select '8','9'
insert into TBL3
select '1','10' union all
select '2','10' union all
select '3','8' union all
select '4','6' union all
select '5','7' union all
select '6','3' union all
select '7','5' union all
select '8','9'
I need to find the PID and the table it is missing from. So the results should look like:
PID
MISSING FROM
1
TBL3
2
TBL3
3
TBL1
4
TBL2
4
TBL3
5
TBL1
6
TBL1
6
TBL2
7
TBL2
10
TBL1
10
TBL2
Thanks all.
View 5 Replies
View Related
Nov 7, 2006
I'm using SQL server 200
Table A has columns CompressedProduct, Tool, Operation
Table B in a differnt database has columns ID, Product, Tool Operation
I cannot edit table A. I can select records from A and insert into B. And I can select only the records that are in both tables.
But I want to be able to select any records that are in table A but not in Table B.
ie. I want to select records from A where the combination of Product, Tool and Operaton does not appear in Table B, even if all 3 on their own do appear.
This code return all the records from A. I need to filter out the records found in Table B.
---------------------------------------------------------------------------------------------------------------------------------
SELECT ID, CompressedProduct, oq.Tool, oq.Operation FROM OPENQUERY (Lisa_Link,
'SELECT DISTINCT CompressedProduct, Tool, Operation FROM tblToolStatus ts
JOIN tblProduct p ON ts.ProductID = p.ProductID
JOIN tblTool t ON ts.ToolID = t.ToolID
JOIN tblOperation o ON ts.OperationID = o.OperationID
WHERE ts.ToolID=66
') oq
LEFT JOIN Family f on oq.CompressedProduct = f.Product and oq.Tool = f.Tool and oq.Operation = f.Operation
View 1 Replies
View Related
Oct 17, 2007
Hi
I wanted to do a delete rows from a group of table. These tables have a common column UserID. I heard that there is something called ondelete cascade. But I don't know how to set it up and utilise it. Could someone tell me how to do it. Or point me to a tutorial which shows how to do it.
Thanks
View 4 Replies
View Related
Jul 23, 2005
Hi,I was wondering if it was possible to build a query that will include acolumn that will provide a count related records from another table.Although there is a way to achieve this through programming in thefront end, I would like to know if it possible to achieve the samething through a SQL statement alone.For example, say you have two related tables, Invoices andInvoiceItems. InvoiceID is the primary key of Invoices.Invoices tableInvoiceID PO_Num CompanyID-------------------------------1 37989 32 87302 43 78942 3InvoiceItems tableItemID InvoiceID PartNo Qty---------------------------------------1 1 ABA 32 1 ASLKDJ 23 1 9LF 84 2 IEPOW 185 2 EIWPD 36 2 DSSIO 17 2 EIWP 58 2 DC93 49 3 85LS0 8Then a query that has the Invoices table plus a count of InvoiceItemsfor each InvoiceID would generate this:InvoiceID PO_Num CompanyID ItemCount-------------------------------------------------1 37989 3 32 87302 4 53 78942 3 1Does anyone have any ideas how this would be done?Thank you.
View 1 Replies
View Related
Mar 20, 2014
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
View 0 Replies
View Related
May 30, 2008
Morning chucky eggsThis is duplicated over at SQLTeam...except I've moved on a bit so my question to you is different:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103798SQL 2k5I have a standard hierarchy in an adjacency table modelling business units in an organisation. I have another table associating people with (one single) business unit. I need to return all the people in each business unit in a cumulative manner i.e. a unit will include all people assigned to that unit and assigned to all child units too.Below is1) Set up code (in red)2) Initial query that failed on last data load due to the nature of the data changing and me not considering the ramifications of my initial solution properly3) My new soulution.My question is - is there a better way to accomplish this? I don't rate my code that highly and could do with this being as efficient as possible as at present I can't see it scaling to larger and larger data volumes all that well.USE tempdbgo----------------------------------------------------------------- Set up the structure & data...---------------------------------------------------------------CREATE TABLE dbo.org ( unit_code VARCHAR(10) NOT NULL , parent_code VARCHAR(10) NULL , CONSTRAINT pk_org PRIMARY KEY CLUSTERED (unit_code) WITH (FILLFACTOR = 100) )GO CREATE TABLE dbo.org_peeps ( unit_code VARCHAR(10) NOT NULL , person_code INT NOT NULL , CONSTRAINT pk_org_peeps PRIMARY KEY CLUSTERED (unit_code, person_code) WITH (FILLFACTOR = 100) , CONSTRAINT fk_org_peeps_other_org_peeps FOREIGN KEY (unit_code) REFERENCES dbo.org (unit_code) )GO CREATE VIEW dbo.org_peeps_parents--WITH SCHEMABINDINGAS SELECT org.unit_code , org.parent_code , org_peeps.person_code FROM dbo.org LEFT OUTER JOIN dbo.org_peeps ON org_peeps.unit_code = org.unit_codeGO INSERT INTO dbo.org (unit_code, parent_code)SELECT 'a', 'y' UNION ALLSELECT 'b', 'y' UNION ALLSELECT 'c', 'z' UNION ALLSELECT 'y', 'z' UNION ALLSELECT 'z', NULL--Insert people data (NOTE - the people are in units at the BOTTOM of the structure only)INSERT INTO dbo.org_peeps (unit_code, person_code)SELECT 'a', 1 UNION ALLSELECT 'c', 3 UNION ALL--These data go into intermediate levels of the organisation...SELECT 'y', 4 UNION ALLSELECT 'y', 5 UNION ALLSELECT 'y', 6---------------------------------------------------------------SELECT 'Original query. Because there is data in the intermediate tables --> ' + 'duplicate outputs....'---------------------------------------------------------------;WITH materialised_pathsAS ( SELECT unit_code , parent_code , person_code , unit_path = '/' + CAST(unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org_peeps_parents WHERE parent_code IS NULL UNION ALL SELECT all_people.unit_code , all_people.parent_code , all_people.person_code , mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org_peeps_parents AS all_people INNER JOIN materialised_paths AS mp ON mp.unit_code = all_people.parent_code )SELECT parents.unit_code , children.person_codeFROM materialised_paths AS childrenCROSS APPLY --Correlated derived table - get the child records per unit ( SELECT unit_code , parent_code FROM dbo.org AS parents_sub WHERE children.unit_path LIKE '%/' + parents_sub.unit_code + '/%' ) AS parentsWHERE children.person_code IS NOT NULLORDER BY person_code , unit_code-----------------------------------------------------------------SELECT 'This query returns the correct results but man it is ugly. '+ 'Can it be refined optimised?'-----------------------------------------------------------------;WITH materialised_pathsAS ( SELECT unit_code , parent_code , unit_path = '/' + CAST(unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org WHERE parent_code IS NULL UNION ALL SELECT all_orgs.unit_code , all_orgs.parent_code , mp.unit_path + CAST(all_orgs.unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org AS all_orgs INNER JOIN materialised_paths AS mp ON mp.unit_code = all_orgs.parent_code )SELECT all_orgs.unit_code , org_peeps.person_codeFROM dbo.org_peepsINNER JOIN ( SELECT org.unit_code , mp.unit_path FROM materialised_paths AS mp CROSS APPLY ( SELECT unit_code , parent_code FROM dbo.org WHERE mp.unit_path LIKE '%/' + org.unit_code + '/%' ) AS org ) AS all_orgsON all_orgs.unit_path LIKE '%/' + org_peeps.unit_code + '/'ORDER BY person_code , all_orgs.unit_code--Clean upIF EXISTS (SELECT NULL FROM sys.views WHERE object_id = OBJECT_ID('dbo.org_peeps_parents')) BEGIN DROP VIEW dbo.org_peeps_parentsENDIF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org_peeps')) BEGIN DROP TABLE dbo.org_peepsENDIF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org')) BEGIN DROP TABLE dbo.orgENDAs ever kudos and lavish thanks to anyone that can help :DOh - I nearly forgot - accounting for the Rudy clause - desired output:unit_code person_code---------- -----------a 1y 1z 1c 3z 3y 4z 4y 5z 5y 6z 6
View 14 Replies
View Related
May 29, 2008
Oh hai
This is closely related to this problem where Ryan & Peter helped me out loads (thanks again guys):
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101989
I now have a slightly different problem. Instead of returning the cumulative counts of people related to a business unit in the hierarchy I now need to return the actual people records. I had some code, derived from the above, that worked fine until I loaded some new data. It took a while but eventually I figured out the (now obvious) reason for the error. I have tried several changes to the query and not got anywhere. Please could someone have a look and see if you can suggest the required alteration(s)?
USE tempdb
go
---------------------------------------------------------------
--Set up the structure & data...
---------------------------------------------------------------
CREATE TABLE dbo.org
(
unit_codeVARCHAR(10)NOT NULL
, parent_codeVARCHAR(10)NULL
, CONSTRAINT pk_orgPRIMARY KEY CLUSTERED (unit_code) WITH (FILLFACTOR = 100)
)
GO
CREATE TABLE dbo.org_peeps
(
unit_codeVARCHAR(10)NOT NULL
, person_codeINTNOT NULL
, CONSTRAINT pk_org_peepsPRIMARY KEY CLUSTERED (unit_code, person_code) WITH (FILLFACTOR = 100)
, CONSTRAINT fk_org_peeps_other_org_peepsFOREIGN KEY (unit_code) REFERENCES dbo.org (unit_code)
)
GO
CREATE VIEW dbo.org_peeps_parents
--WITH SCHEMABINDING
AS
SELECT org.unit_code
, org.parent_code
, org_peeps.person_code
FROMdbo.org
LEFT OUTER JOIN
dbo.org_peeps
ONorg_peeps.unit_code= org.unit_code
GO
INSERT INTO dbo.org (unit_code, parent_code)
SELECT 'a', 'y'UNION ALL
SELECT 'b', 'y'UNION ALL
SELECT 'c', 'z'UNION ALL
SELECT 'y', 'z'UNION ALL
SELECT 'z', NULL
--Insert people data (NOTE - the people are in units at the BOTTOM of the structure only)
INSERT INTO dbo.org_peeps (unit_code, person_code)
SELECT 'a', 1UNION ALL
SELECT 'c', 3
---------------------------------------------------------------
SELECT'This works great....'
---------------------------------------------------------------
;WITHmaterialised_paths
AS
(
SELECTunit_code
, parent_code
, person_code
, unit_path= '/' + CAST(unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents
WHEREparent_code IS NULL
UNION ALL
SELECTall_people.unit_code
, all_people.parent_code
, all_people.person_code
, mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents AS all_people
INNER JOIN
materialised_paths AS mp
ONmp.unit_code= all_people.parent_code
)
SELECTparents.unit_code
, children.person_code
, children.unit_path
FROMmaterialised_paths AS children
CROSS APPLY--Correlated derived table - get the child records per unit
(
SELECTunit_code
, parent_code
FROMdbo.orgAS parents_sub
WHEREchildren.unit_path LIKE '%/' + parents_sub.unit_code + '/%'
) AS parents
WHEREchildren.person_code IS NOT NULL
ORDER BY person_code
, unit_code
--Add data to INTERMEDIATE levels of organisation
INSERT INTO dbo.org_peeps (unit_code, person_code)
SELECT 'y', 4UNION ALL
SELECT 'y', 5UNION ALL
SELECT 'y', 6
---------------------------------------------------------------
SELECT'Since there is now data in the intermediate tables there are ' +
'duplicate outputs....'
---------------------------------------------------------------
;WITHmaterialised_paths
AS
(
SELECTunit_code
, parent_code
, person_code
, unit_path= '/' + CAST(unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents
WHEREparent_code IS NULL
UNION ALL
SELECTall_people.unit_code
, all_people.parent_code
, all_people.person_code
, mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents AS all_people
INNER JOIN
materialised_paths AS mp
ONmp.unit_code= all_people.parent_code
)
SELECTparents.unit_code
, children.person_code
, children.unit_path
FROMmaterialised_paths AS children
CROSS APPLY--Correlated derived table - get the child records per unit
(
SELECTunit_code
, parent_code
FROMdbo.orgAS parents_sub
WHEREchildren.unit_path LIKE '%/' + parents_sub.unit_code + '/%'
) AS parents
WHEREchildren.person_code IS NOT NULL
ORDER BY person_code
, unit_code
---------------------------------------------------------------
SELECT'This is the output I want - but I can''t simply apply distinct - the volumes of data preclude this'
---------------------------------------------------------------
;WITHmaterialised_paths
AS
(
SELECTunit_code
, parent_code
, person_code
, unit_path= '/' + CAST(unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents
WHEREparent_code IS NULL
UNION ALL
SELECTall_people.unit_code
, all_people.parent_code
, all_people.person_code
, mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents AS all_people
INNER JOIN
materialised_paths AS mp
ONmp.unit_code= all_people.parent_code
)
SELECTDISTINCT
parents.unit_code
, children.person_code
, children.unit_path
FROMmaterialised_paths AS children
CROSS APPLY--Correlated derived table - get the child records per unit
(
SELECTunit_code
, parent_code
FROMdbo.orgAS parents_sub
WHEREchildren.unit_path LIKE '%/' + parents_sub.unit_code + '/%'
) AS parents
WHEREchildren.person_code IS NOT NULL
ORDER BY person_code
, unit_code
--Clean up
IF EXISTS (SELECT NULL FROM sys.views WHERE object_id = OBJECT_ID('dbo.org_peeps_parents')) BEGIN
DROP VIEW dbo.org_peeps_parents
END
IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org_peeps')) BEGIN
DROP TABLE dbo.org_peeps
END
IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org')) BEGIN
DROP TABLE dbo.org
ENDJust paste and run the code and hopefully it should be reasonably clear. If you need any further info please let me know
View 5 Replies
View Related
Aug 23, 2007
The problem of mine is, I have a datagrid, Which displays data from a Employee(parent) table.
Now I want to delete some records based on the user selected checkbox,only those records which has no related records in the EmployeeProject(child) can be deleted.I want to know which are all the record that cannot be deleted?
How can I achieve this?
View 3 Replies
View Related
Apr 15, 2008
Hi guysI'm mulling over the best way to do something and would like your input. Forgive me if this is a bit 101 - I haven't ever had to do this in SQL before!Fairly standard set up - Hierarchy table modelling the structure of an organisation.Related table associating members of staff to the hierarchy.I want to return all levels of the hierarchy and for each level I would like to know the total number of people in the level (so for a division it would be the sum of all people in the child teams).Parameters - This table will be modelling many organisations' structures - I cannot guarentee anything like "there will never be more than n levels". As such - I would strongly prefer to have something that is iterative recursive. I can change the schema to suit the method I use if necessary. Database is not transactional - I am not concerned about updating speed. SQL Server 2K5.I've tried CTE but it turns out you cannot use group by in CTEs (even in derived tables). I have not yet tried feeding it a view or similar.I have not tried nested sets, materialised paths, accumulator table - I thought I would see if there is something obvious before I start piddling around with those.Ta!
View 11 Replies
View Related
Apr 30, 2008
SQL Server 2005.
Schema is not fixed at this stage - small project, schema can be adapted if neccessary.
This needs to be produced in SQL Server - there is no client application.
Hi
I am really struggling to write the SQL for the following requirement and have got to the stage where I think I need someone to give me some pointers. I can't believe this is a novel requirement but I haven't had to do this before nor can find anything similar when googling.
I have a hierarchy detailing an organisation structure. Eventually the table will store many structures and these may one day be "pseudo structures" to enable really granular reporting. As such I cannot really say "there will never be more than N levels to this hierarchy". Currently I'm using a standard adjacency design. From now on I'll call the items in the table "business units".
There is a further table with a related FK declared which associates people with business units.
Requirement: Return all business units and cumulative total total of employees for each of these units (i.e. the total for each business unit will be the number of people in that unit plus the sum of the people in all the child units).
I've asked this question on dbforums but ended up a blind alley despite Peter's attempt to help. I'm starting again from scratch but feel free to review the (now dead) thread here:
http://www.dbforums.com/showthread.php?t=1629366
To summarise - I tried the CTE route but of course this evaluates the hierarchy from the top down. To fulfill the requirement, the bottom of the structure needs to be the starting point. I also tried reversing the hierarchy so that children, rather than the parent, are stored but my query fails if there are 3+ levels.
Code to reproduce problem (note - I expect that I will need to get the employee count per business unit prior to working with the hierarchy so I have flattened the schema to reflect this - I think I can handle the SQL to get the non-cumulative count per business unit ):
DECLARE @table TABLE
(
unit_codeTINYINT
, parent_codeTINYINT
, headcountINT
, PRIMARY KEY CLUSTERED (unit_code)
)
INSERT INTO @table (unit_code, parent_code, headcount)
SELECT 1, 4, 10 UNION ALL
SELECT 2, 4, 130 UNION ALL
SELECT 3, 10, 93 UNION ALL
SELECT 4, 10, 7 UNION ALL
SELECT 10, NULL, 1
Required output:
unit_codecumulative_headcount
------------------------------------
110
2130
393
4147
10241
Many thanks
View 5 Replies
View Related
Nov 19, 2015
I need a boost to understand how to get started on solving this SQL Query.
TicketId Seq StatusDateTime
T10001 1 2015-05-04 15:54:00.000
T10001 2 2015-05-04 16:35:00.000
T10001 3 2015-05-04 16:42:00.000
T10001 4 2015-05-05 08:19:00.000
T10002 1 2015-05-04 15:56:00.000
T10002 2 2015-05-04 15:57:00.000
I need to find the total StatusDateTime for each TicketId
I need to find the average StatusDateTime for all TicketIDs
Ex. TicketId, "T10001", has 4 records based on the Seq column.
By using this, I should be able to find the amount of time between the first Seq and the last Seq to get a total time span for Ticket.
Expanding on this, I should be able to add up all of the Ticket's calculated time spans and divide by the number of tickets to get the average time span.
View 6 Replies
View Related
Sep 5, 2007
This is an easy one, I hope. Terminology has changed over the years, and I'm not sure what the current nomenclature is with regards to ...
I have a table contains State names and State codes. I have another that contains Product names and Product codes. I have another that contains Collateral type names, and Collateral type codes.
At one time, I heard these referred to as domain tables, but I don't believe that was a correct label today. We do want to differentiate these tables from other tables that might refer to them. Other tables might be transaction tables, or represent data that will eventually be purged. However, Collateral types are somewhat static in a business environment. So are the names and number of states. Likewise with Countries. Even Products are somewhat static, and certain ProductTypes (or category) might be static.
So what is the name that refers to these types of data tables, as opposed to the more transactional data?
View 2 Replies
View Related
Sep 29, 2015
I have a table that has multiple transactions for stock items.
This table holds all records relating to items that are inducted onto the system and there movement. For each stock item i am interested in getting the drop destination, if it has one, and only when it follows the sequential order of "Inducted>OnTransport>Dropped" (this sequence isn't always the case). Also note the CreatedDate for the Inducted and OnTransport records for the valid sequences are always the same. Below is a valid sequence for a stock item so i would want to return 'Lane01' for the Destination of this occurrence of the stock item, if this item didn't have a valid drop location then destination would be blank. Also note each stock item can be inducted more than one time per-day.
I think i have managed to build the below sql but it will only do one item at a time, so would have to wrap it in a function. Is there a way of writing a set based select statement that gets all the inducted items and for the ones that do follow the "Inducted>OnTransport>Dropped" return the destination it was dropped at? I've attached scrips below:
DECLARE @StockItemID nVarchar(128)
DECLARE @CreateDate DATETIME
Set @StockItemID='8cbe17da-6079-4170-b27a-41c0d38830f6'
Set @CreateDate = CAST('2015-08-31 13:52:39.890' AS datetime)
[Code] ....
View 9 Replies
View Related
Sep 24, 2007
Thanks for your time,
How to calculate & save a Parent status [qcStatus varchar(30)] and Alert [alertFlag bit] in dbo.a1_qcParent
based on comparison of its Child records in dbo.a3_qcItems2Fix columns [itemComplete bit] and [alertFlag bit]
Where a1_qcParent[a1_id] = a3_qcItems2Fix[a1_ID]
- Parent CLOSED: if all children [itemComplete] are True
- Parent OPEN: if any child [itemComplete] is False
- Parent ALERT: True if any child row [alertFlag bit] is True
Using sql_Datasource in webpage, but more comfortable in sql... After-Trigger?
Can Parent columns have calculated formula referencing the child table? Please help.
View 1 Replies
View Related