Hi,
I have a table like this:
tblDescendants
ParentID ChildID
A B
A C
B D
D E
So this table contains parent-child relations, but they can go infinite
long.
What I need is a select-query, that returns me for al the 'original'
parents, their relatiosn with their cildren, grandchildren, and further
descendants etc...
So with this records:
ParentID DescendantID
A B
A C
A D (because D is a child of B which is a child of A)
A E (same reason).
How can I do this? I need to use this select in a join. I have a solution
with a temporary table, but I would prefer not to have to make this
temporary table each time I run my query... I would prefer a solution with
an Indexed View.
Does anybody knows how? Any help will be really appreciated!
Thansk a lot in advance,
PieterHi
Take a look at Itzik Ben-Gan's examples
IF object_id('dbo.Employees') IS NOT NULL
DROP TABLE Employees
GO
IF object_id('dbo.ufn_GetSubtree') IS NOT NULL
DROP FUNCTION dbo.ufn_GetSubtree
GO
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
/*
empid mgrid empname salary
2 1 Andrew 5000.0000
5 2 Steven 2500.0000
6 2 Michael 2500.0000
*/
--SQL Server 2005--
With TreeCTE (empid, mgrid, empname, salary, lvl, [path]) as
(
select empid, mgrid, empname, salary, 0 as lvl, cast('' as varchar(200))
as [path]
from Employees
union all
select tc.empid, e.mgrid, tc.empname, tc.salary, lvl + 1,
cast([path]+cast(e.empid as varchar(20))+'/' as varchar(200))
from Employees e
inner join TreeCTE tc on e.empid = tc.mgrid
)
select * from TreeCTE where mgrid = 1
----
/*
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
*/
/*
employee
--
Nancy
| Andrew
| | Steven
| | Michael
| Janet
| | Robert
| | | David
| | | | James
| | | Ron
| | | Dan
| | Laura
| | Ann
| Margaret
| | Ina
*/
"Pieter" <pieterNOSPAMcoucke@.hotmail.com> wrote in message
news:%23orHcToIHHA.1064@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a table like this:
> tblDescendants
> ParentID ChildID
> A B
> A C
> B D
> D E
>
> So this table contains parent-child relations, but they can go infinite
> long.
> What I need is a select-query, that returns me for al the 'original'
> parents, their relatiosn with their cildren, grandchildren, and further
> descendants etc...
> So with this records:
> ParentID DescendantID
> A B
> A C
> A D (because D is a child of B which is a child of A)
> A E (same reason).
> How can I do this? I need to use this select in a join. I have a solution
> with a temporary table, but I would prefer not to have to make this
> temporary table each time I run my query... I would prefer a solution with
> an Indexed View.
> Does anybody knows how? Any help will be really appreciated!
> Thansk a lot in advance,
> Pieter
>|||Ok thanks, putting it all in a function works :-)
Showing posts with label child. Show all posts
Showing posts with label child. Show all posts
Friday, March 30, 2012
query: child of child of child of...: Which are the descendants?
Hi,
I have a table like this:
tblDescendants
ParentID ChildID
A B
A C
B D
D E
So this table contains parent-child relations, but they can go infinite
long.
What I need is a select-query, that returns me for al the 'original'
parents, their relatiosn with their cildren, grandchildren, and further
descendants etc...
So with this records:
ParentID DescendantID
A B
A C
A D (because D is a child of B which is a child of A)
A E (same reason).
How can I do this? I need to use this select in a join. I have a solution
with a temporary table, but I would prefer not to have to make this
temporary table each time I run my query... I would prefer a solution with
an Indexed View.
Does anybody knows how? Any help will be really appreciated!
Thansk a lot in advance,
PieterHi
Take a look at Itzik Ben-Gan's examples
IF object_id('dbo.Employees') IS NOT NULL
DROP TABLE Employees
GO
IF object_id('dbo.ufn_GetSubtree') IS NOT NULL
DROP FUNCTION dbo.ufn_GetSubtree
GO
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
/*
empid mgrid empname salary
2 1 Andrew 5000.0000
5 2 Steven 2500.0000
6 2 Michael 2500.0000
*/
--SQL Server 2005--
With TreeCTE (empid, mgrid, empname, salary, lvl, [path]) as
(
select empid, mgrid, empname, salary, 0 as lvl, cast('' as varchar(200))
as [path]
from Employees
union all
select tc.empid, e.mgrid, tc.empname, tc.salary, lvl + 1,
cast([path]+cast(e.empid as varchar(20))+'/' as varchar(200))
from Employees e
inner join TreeCTE tc on e.empid = tc.mgrid
)
select * from TreeCTE where mgrid = 1
----
/*
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
*/
/*
employee
--
Nancy
| Andrew
| | Steven
| | Michael
| Janet
| | Robert
| | | David
| | | | James
| | | Ron
| | | Dan
| | Laura
| | Ann
| Margaret
| | Ina
*/
"Pieter" <pieterNOSPAMcoucke@.hotmail.com> wrote in message
news:%23orHcToIHHA.1064@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a table like this:
> tblDescendants
> ParentID ChildID
> A B
> A C
> B D
> D E
>
> So this table contains parent-child relations, but they can go infinite
> long.
> What I need is a select-query, that returns me for al the 'original'
> parents, their relatiosn with their cildren, grandchildren, and further
> descendants etc...
> So with this records:
> ParentID DescendantID
> A B
> A C
> A D (because D is a child of B which is a child of A)
> A E (same reason).
> How can I do this? I need to use this select in a join. I have a solution
> with a temporary table, but I would prefer not to have to make this
> temporary table each time I run my query... I would prefer a solution with
> an Indexed View.
> Does anybody knows how? Any help will be really appreciated!
> Thansk a lot in advance,
> Pieter
>|||Ok thanks, putting it all in a function works :-)sql
I have a table like this:
tblDescendants
ParentID ChildID
A B
A C
B D
D E
So this table contains parent-child relations, but they can go infinite
long.
What I need is a select-query, that returns me for al the 'original'
parents, their relatiosn with their cildren, grandchildren, and further
descendants etc...
So with this records:
ParentID DescendantID
A B
A C
A D (because D is a child of B which is a child of A)
A E (same reason).
How can I do this? I need to use this select in a join. I have a solution
with a temporary table, but I would prefer not to have to make this
temporary table each time I run my query... I would prefer a solution with
an Indexed View.
Does anybody knows how? Any help will be really appreciated!
Thansk a lot in advance,
PieterHi
Take a look at Itzik Ben-Gan's examples
IF object_id('dbo.Employees') IS NOT NULL
DROP TABLE Employees
GO
IF object_id('dbo.ufn_GetSubtree') IS NOT NULL
DROP FUNCTION dbo.ufn_GetSubtree
GO
CREATE TABLE Employees
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
CREATE FUNCTION dbo.ufn_GetSubtree
(
@.mgrid AS int
)
RETURNS @.tree table
(
empid int NOT NULL,
mgrid int NULL,
empname varchar(25) NOT NULL,
salary money NOT NULL,
lvl int NOT NULL,
path varchar(900) NOT NULL
)
AS
BEGIN
DECLARE @.lvl AS int, @.path AS varchar(900)
SELECT @.lvl = 0, @.path = '.'
INSERT INTO @.tree
SELECT empid, mgrid, empname, salary,
@.lvl, '.' + CAST(empid AS varchar(10)) + '.'
FROM Employees
WHERE empid = @.mgrid
WHILE @.@.ROWCOUNT > 0
BEGIN
SET @.lvl = @.lvl + 1
INSERT INTO @.tree
SELECT E.empid, E.mgrid, E.empname, E.salary,
@.lvl, T.path + CAST(E.empid AS varchar(10)) + '.'
FROM Employees AS E JOIN @.tree AS T
ON E.mgrid = T.empid AND T.lvl = @.lvl - 1
END
RETURN
END
GO
SELECT empid, mgrid, empname, salary
FROM ufn_GetSubtree(3)
GO
/*
empid mgrid empname salary
2 1 Andrew 5000.0000
5 2 Steven 2500.0000
6 2 Michael 2500.0000
*/
--SQL Server 2005--
With TreeCTE (empid, mgrid, empname, salary, lvl, [path]) as
(
select empid, mgrid, empname, salary, 0 as lvl, cast('' as varchar(200))
as [path]
from Employees
union all
select tc.empid, e.mgrid, tc.empname, tc.salary, lvl + 1,
cast([path]+cast(e.empid as varchar(20))+'/' as varchar(200))
from Employees e
inner join TreeCTE tc on e.empid = tc.mgrid
)
select * from TreeCTE where mgrid = 1
----
/*
SELECT REPLICATE (' | ', lvl) + empname AS employee
FROM ufn_GetSubtree(1)
ORDER BY path
*/
/*
employee
--
Nancy
| Andrew
| | Steven
| | Michael
| Janet
| | Robert
| | | David
| | | | James
| | | Ron
| | | Dan
| | Laura
| | Ann
| Margaret
| | Ina
*/
"Pieter" <pieterNOSPAMcoucke@.hotmail.com> wrote in message
news:%23orHcToIHHA.1064@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a table like this:
> tblDescendants
> ParentID ChildID
> A B
> A C
> B D
> D E
>
> So this table contains parent-child relations, but they can go infinite
> long.
> What I need is a select-query, that returns me for al the 'original'
> parents, their relatiosn with their cildren, grandchildren, and further
> descendants etc...
> So with this records:
> ParentID DescendantID
> A B
> A C
> A D (because D is a child of B which is a child of A)
> A E (same reason).
> How can I do this? I need to use this select in a join. I have a solution
> with a temporary table, but I would prefer not to have to make this
> temporary table each time I run my query... I would prefer a solution with
> an Indexed View.
> Does anybody knows how? Any help will be really appreciated!
> Thansk a lot in advance,
> Pieter
>|||Ok thanks, putting it all in a function works :-)sql
Tuesday, March 20, 2012
query to populate child tables
hi all, can anyone help me?
I am a relative newbie to sql server and I am more familiar with
Enterprise Manager than QA. I have made many many access databases
though. I am making an asp.net application where by there are a set
number of users, about 80, each one logs in and manages information
within their department.
To get them started a manager has written 10 different hazards that
will apply to all of the departments, and he has written consequences
and controls for the hazards. Each department must have this
information as each will manage and deal with them differently
The hazard information is stored in a main 'hazards' table, and the
consequences and controls are stored in related tables linked by the
'hazardID' from the main table to a foreign key 'hazardID' in the
related tables
What i want to know is if there is a relatively simple way of using a
query to populate the 10 hazards to each department, and to also
include the related table links, i dont mind renaming the departments
names to match each hazard, but i do not want to have to relink the
related tables manually
If anyone can give me any advice to get me started i will be incredibly
grateful
thank you
Table information is below
Hazards
--
HazardID - identity key field
Hazard - varchar
Department - varchar
Consequences
--
ConsequenceID - identity key field
HazardID - FK
Consequence - varchar
Controls
--
ControlID - identity key field
HazardID - FK
Control - varchar
dwightIf I understand you correctly, you want to retreive the Hazard Information a
nd include the Consequences and Controls for each Hazard, and you want to li
mit this by Department. But you also indicated that there were 10 Hazards th
at were common to all Departments. If you want a list of Hazards by Departme
nt, this may work:
SELECT
h.HazardID
, h.Hazard
, cq.Consequence
, cn.Control
FROM Hazards h
JOIN Consequences cq
ON h.HazardID = cq.HazardID
JOIN Conrols cn
ON h.HazardID = cn.HazardID
WHERE h.Department = <Department>
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<dwightsmail@.gmail.com> wrote in message news:1153284544.918469.266560@.i3g2000cwc.googlegrou
ps.com...
>
> hi all, can anyone help me?
>
> I am a relative newbie to sql server and I am more familiar with
> Enterprise Manager than QA. I have made many many access databases
> though. I am making an asp.net application where by there are a set
> number of users, about 80, each one logs in and manages information
> within their department.
>
> To get them started a manager has written 10 different hazards that
> will apply to all of the departments, and he has written consequences
> and controls for the hazards. Each department must have this
> information as each will manage and deal with them differently
>
> The hazard information is stored in a main 'hazards' table, and the
> consequences and controls are stored in related tables linked by the
> 'hazardID' from the main table to a foreign key 'hazardID' in the
> related tables
>
> What i want to know is if there is a relatively simple way of using a
> query to populate the 10 hazards to each department, and to also
> include the related table links, i dont mind renaming the departments
> names to match each hazard, but i do not want to have to relink the
> related tables manually
>
> If anyone can give me any advice to get me started i will be incredibly
> grateful
>
> thank you
>
>
> Table information is below
>
> Hazards
> --
> HazardID - identity key field
> Hazard - varchar
> Department - varchar
>
> Consequences
> --
> ConsequenceID - identity key field
> HazardID - FK
> Consequence - varchar
>
> Controls
> --
> ControlID - identity key field
> HazardID - FK
> Control - varchar
>
>
>
> dwight
>|||Hi Arnie
Thank you for the response
I find it hard to explain things like this
What I want to do is to 'append' the hazards, i will manually then go
through them and change the departments so that they are unique for
each department to log in
When I append them I want them to take the new HazardID to the related
tables
So to start with, I have 10 hazards with related consequences and
controls
For an example, I want to repeat those 10 hazards 10 times so that i
can assign them to 10 different departments
When I append the 10 hazards once each for each department I want the
controls and consequences to also append to their tables and for the
HazardID link the tables to the related tables
I know this is repeating data, but it is only to initialise the system
for the users and they will then go on to manage them differently
Does that make sense now?
thank you so very much for your assistance
Arnie Rowland wrote:
> If I understand you correctly, you want to retreive the Hazard Information and inc
lude the Consequences and Controls for each Hazard, and you want to limit this by De
partment. But you also indicated that there were 10 Hazards that were common to all
Dep
artments. If you want a list of Hazards by Department, this may work:
>
> SELECT
> h.HazardID
> , h.Hazard
> , cq.Consequence
> , cn.Control
> FROM Hazards h
> JOIN Consequences cq
> ON h.HazardID = cq.HazardID
> JOIN Conrols cn
> ON h.HazardID = cn.HazardID
> WHERE h.Department = <Department>
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <dwightsmail@.gmail.com> wrote in message news:1153284544.918469.266560@.i3g
2000cwc.googlegroups.com...
> --=_NextPart_000_00FF_01C6AABD.53A28AE0
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 4045
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> <META content="MSHTML 6.00.5296.0" name=GENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY>
> <DIV><FONT face=Arial size=2>If I understand you correctly, you want to re
treive
> the Hazard Information and include the Consequences and Controls for each
> Hazard, and you want to limit this by Department. But you also indicated t
hat
> there were 10 Hazards that were common to all Departments. If you want a l
ist of
> Hazards by Department, this may work:</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2><BR><FONT face="Courier New">SELECT
> <BR> h.HazardID<BR> &
nbsp; ,
> h.Hazard<BR> , cq.Consequence<BR>
, cn.Control<BR>FROM
> Hazards h<BR> JOIN Consequences cq<BR> &n
bsp;
> ON h.HazardID = cq.HazardID<BR> JOIN Conrols
> cn<BR> ON h.HazardID = c
n.HazardID<BR>WHERE
> h.Department = <Department></FONT></FONT></DIV>
> <DIV><BR><FONT face=Arial size=2>-- <BR>Arnie Rowland<BR>Most good judgmen
t
> comes from experience. <BR>Most experience comes from bad judgment. <BR>-
> Anonymous</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2><</FONT><A
> href="http://links.10026.com/?link=mailto:dwightsmail@.gmail.com"><FONT face=Arial
> size=2>dwightsmail@.gmail.com</FONT></A><FONT face=Arial size=2>> wrote
in
> message </FONT><A
> href="http://links.10026.com/?link=news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com"><FONT
> face=Arial
> size=2>news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com</FONT></A
><FONT
> face=Arial size=2>...</FONT></DIV><FONT face=Arial size=2>> <BR>> hi
all,
> can anyone help me?<BR>> <BR>> I am a relative newbie to sql server
and I
> am more familiar with<BR>> Enterprise Manager than QA. I have made many
many
> access databases<BR>> though. I am making an asp.net application where
by
> there are a set<BR>> number of users, about 80, each one logs in and ma
nages
> information<BR>> within their department.<BR>> <BR>> To get them
> started a manager has written 10 different hazards that<BR>> will apply
to
> all of the departments, and he has written consequences<BR>> and contro
ls for
> the hazards. Each department must have this<BR>> information as each wi
ll
> manage and deal with them differently<BR>> <BR>> The hazard informat
ion is
> stored in a main 'hazards' table, and the<BR>> consequences and control
s are
> stored in related tables linked by the<BR>> 'hazardID' from the main ta
ble to
> a foreign key 'hazardID' in the<BR>> related tables<BR>> <BR>> Wh
at i
> want to know is if there is a relatively simple way of using a<BR>> que
ry to
> populate the 10 hazards to each department, and to also<BR>> include th
e
> related table links, i dont mind renaming the departments<BR>> names to
match
> each hazard, but i do not want to have to relink the<BR>> related table
s
> manually<BR>> <BR>> If anyone can give me any advice to get me start
ed i
> will be incredibly<BR>> grateful<BR>> <BR>> thank you<BR>> <BR
>>
> <BR>> Table information is below<BR>> <BR>> Hazards<BR>>
> --<BR>> HazardID - identity key field<BR>> Hazard -
> varchar<BR>> Department - varchar<BR>> <BR>> Consequences<BR>>
> --<BR>> ConsequenceID - identity key field<BR>>
> HazardID - FK<BR>> Consequence - varchar<BR>> <BR>> Controls<BR>&
gt;
> --<BR>> ControlID - identity key field<BR>> HazardID -
> FK<BR>> Control - varchar<BR>> <BR>> <BR>> <BR>>
> dwight<BR>></FONT></BODY></HTML>
> --=_NextPart_000_00FF_01C6AABD.53A28AE0--
I am a relative newbie to sql server and I am more familiar with
Enterprise Manager than QA. I have made many many access databases
though. I am making an asp.net application where by there are a set
number of users, about 80, each one logs in and manages information
within their department.
To get them started a manager has written 10 different hazards that
will apply to all of the departments, and he has written consequences
and controls for the hazards. Each department must have this
information as each will manage and deal with them differently
The hazard information is stored in a main 'hazards' table, and the
consequences and controls are stored in related tables linked by the
'hazardID' from the main table to a foreign key 'hazardID' in the
related tables
What i want to know is if there is a relatively simple way of using a
query to populate the 10 hazards to each department, and to also
include the related table links, i dont mind renaming the departments
names to match each hazard, but i do not want to have to relink the
related tables manually
If anyone can give me any advice to get me started i will be incredibly
grateful
thank you
Table information is below
Hazards
--
HazardID - identity key field
Hazard - varchar
Department - varchar
Consequences
--
ConsequenceID - identity key field
HazardID - FK
Consequence - varchar
Controls
--
ControlID - identity key field
HazardID - FK
Control - varchar
dwightIf I understand you correctly, you want to retreive the Hazard Information a
nd include the Consequences and Controls for each Hazard, and you want to li
mit this by Department. But you also indicated that there were 10 Hazards th
at were common to all Departments. If you want a list of Hazards by Departme
nt, this may work:
SELECT
h.HazardID
, h.Hazard
, cq.Consequence
, cn.Control
FROM Hazards h
JOIN Consequences cq
ON h.HazardID = cq.HazardID
JOIN Conrols cn
ON h.HazardID = cn.HazardID
WHERE h.Department = <Department>
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<dwightsmail@.gmail.com> wrote in message news:1153284544.918469.266560@.i3g2000cwc.googlegrou
ps.com...
>
> hi all, can anyone help me?
>
> I am a relative newbie to sql server and I am more familiar with
> Enterprise Manager than QA. I have made many many access databases
> though. I am making an asp.net application where by there are a set
> number of users, about 80, each one logs in and manages information
> within their department.
>
> To get them started a manager has written 10 different hazards that
> will apply to all of the departments, and he has written consequences
> and controls for the hazards. Each department must have this
> information as each will manage and deal with them differently
>
> The hazard information is stored in a main 'hazards' table, and the
> consequences and controls are stored in related tables linked by the
> 'hazardID' from the main table to a foreign key 'hazardID' in the
> related tables
>
> What i want to know is if there is a relatively simple way of using a
> query to populate the 10 hazards to each department, and to also
> include the related table links, i dont mind renaming the departments
> names to match each hazard, but i do not want to have to relink the
> related tables manually
>
> If anyone can give me any advice to get me started i will be incredibly
> grateful
>
> thank you
>
>
> Table information is below
>
> Hazards
> --
> HazardID - identity key field
> Hazard - varchar
> Department - varchar
>
> Consequences
> --
> ConsequenceID - identity key field
> HazardID - FK
> Consequence - varchar
>
> Controls
> --
> ControlID - identity key field
> HazardID - FK
> Control - varchar
>
>
>
> dwight
>|||Hi Arnie
Thank you for the response
I find it hard to explain things like this
What I want to do is to 'append' the hazards, i will manually then go
through them and change the departments so that they are unique for
each department to log in
When I append them I want them to take the new HazardID to the related
tables
So to start with, I have 10 hazards with related consequences and
controls
For an example, I want to repeat those 10 hazards 10 times so that i
can assign them to 10 different departments
When I append the 10 hazards once each for each department I want the
controls and consequences to also append to their tables and for the
HazardID link the tables to the related tables
I know this is repeating data, but it is only to initialise the system
for the users and they will then go on to manage them differently
Does that make sense now?
thank you so very much for your assistance
Arnie Rowland wrote:
> If I understand you correctly, you want to retreive the Hazard Information and inc
lude the Consequences and Controls for each Hazard, and you want to limit this by De
partment. But you also indicated that there were 10 Hazards that were common to all
Dep
artments. If you want a list of Hazards by Department, this may work:
>
> SELECT
> h.HazardID
> , h.Hazard
> , cq.Consequence
> , cn.Control
> FROM Hazards h
> JOIN Consequences cq
> ON h.HazardID = cq.HazardID
> JOIN Conrols cn
> ON h.HazardID = cn.HazardID
> WHERE h.Department = <Department>
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <dwightsmail@.gmail.com> wrote in message news:1153284544.918469.266560@.i3g
2000cwc.googlegroups.com...
> --=_NextPart_000_00FF_01C6AABD.53A28AE0
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 4045
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> <META content="MSHTML 6.00.5296.0" name=GENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY>
> <DIV><FONT face=Arial size=2>If I understand you correctly, you want to re
treive
> the Hazard Information and include the Consequences and Controls for each
> Hazard, and you want to limit this by Department. But you also indicated t
hat
> there were 10 Hazards that were common to all Departments. If you want a l
ist of
> Hazards by Department, this may work:</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2><BR><FONT face="Courier New">SELECT
> <BR> h.HazardID<BR> &
nbsp; ,
> h.Hazard<BR> , cq.Consequence<BR>
, cn.Control<BR>FROM
> Hazards h<BR> JOIN Consequences cq<BR> &n
bsp;
> ON h.HazardID = cq.HazardID<BR> JOIN Conrols
> cn<BR> ON h.HazardID = c
n.HazardID<BR>WHERE
> h.Department = <Department></FONT></FONT></DIV>
> <DIV><BR><FONT face=Arial size=2>-- <BR>Arnie Rowland<BR>Most good judgmen
t
> comes from experience. <BR>Most experience comes from bad judgment. <BR>-
> Anonymous</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2><</FONT><A
> href="http://links.10026.com/?link=mailto:dwightsmail@.gmail.com"><FONT face=Arial
> size=2>dwightsmail@.gmail.com</FONT></A><FONT face=Arial size=2>> wrote
in
> message </FONT><A
> href="http://links.10026.com/?link=news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com"><FONT
> face=Arial
> size=2>news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com</FONT></A
><FONT
> face=Arial size=2>...</FONT></DIV><FONT face=Arial size=2>> <BR>> hi
all,
> can anyone help me?<BR>> <BR>> I am a relative newbie to sql server
and I
> am more familiar with<BR>> Enterprise Manager than QA. I have made many
many
> access databases<BR>> though. I am making an asp.net application where
by
> there are a set<BR>> number of users, about 80, each one logs in and ma
nages
> information<BR>> within their department.<BR>> <BR>> To get them
> started a manager has written 10 different hazards that<BR>> will apply
to
> all of the departments, and he has written consequences<BR>> and contro
ls for
> the hazards. Each department must have this<BR>> information as each wi
ll
> manage and deal with them differently<BR>> <BR>> The hazard informat
ion is
> stored in a main 'hazards' table, and the<BR>> consequences and control
s are
> stored in related tables linked by the<BR>> 'hazardID' from the main ta
ble to
> a foreign key 'hazardID' in the<BR>> related tables<BR>> <BR>> Wh
at i
> want to know is if there is a relatively simple way of using a<BR>> que
ry to
> populate the 10 hazards to each department, and to also<BR>> include th
e
> related table links, i dont mind renaming the departments<BR>> names to
match
> each hazard, but i do not want to have to relink the<BR>> related table
s
> manually<BR>> <BR>> If anyone can give me any advice to get me start
ed i
> will be incredibly<BR>> grateful<BR>> <BR>> thank you<BR>> <BR
>>
> <BR>> Table information is below<BR>> <BR>> Hazards<BR>>
> --<BR>> HazardID - identity key field<BR>> Hazard -
> varchar<BR>> Department - varchar<BR>> <BR>> Consequences<BR>>
> --<BR>> ConsequenceID - identity key field<BR>>
> HazardID - FK<BR>> Consequence - varchar<BR>> <BR>> Controls<BR>&
gt;
> --<BR>> ControlID - identity key field<BR>> HazardID -
> FK<BR>> Control - varchar<BR>> <BR>> <BR>> <BR>>
> dwight<BR>></FONT></BODY></HTML>
> --=_NextPart_000_00FF_01C6AABD.53A28AE0--
query to populate child tables
hi all, can anyone help me?
I am a relative newbie to sql server and I am more familiar with
Enterprise Manager than QA. I have made many many access databases
though. I am making an asp.net application where by there are a set
number of users, about 80, each one logs in and manages information
within their department.
To get them started a manager has written 10 different hazards that
will apply to all of the departments, and he has written consequences
and controls for the hazards. Each department must have this
information as each will manage and deal with them differently
The hazard information is stored in a main 'hazards' table, and the
consequences and controls are stored in related tables linked by the
'hazardID' from the main table to a foreign key 'hazardID' in the
related tables
What i want to know is if there is a relatively simple way of using a
query to populate the 10 hazards to each department, and to also
include the related table links, i dont mind renaming the departments
names to match each hazard, but i do not want to have to relink the
related tables manually
If anyone can give me any advice to get me started i will be incredibly
grateful
thank you
Table information is below
Hazards
--
HazardID - identity key field
Hazard - varchar
Department - varchar
Consequences
--
ConsequenceID - identity key field
HazardID - FK
Consequence - varchar
Controls
--
ControlID - identity key field
HazardID - FK
Control - varchar
dwightThis is a multi-part message in MIME format.
--=_NextPart_000_00FF_01C6AABD.53A28AE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
If I understand you correctly, you want to retreive the Hazard =Information and include the Consequences and Controls for each Hazard, =and you want to limit this by Department. But you also indicated that =there were 10 Hazards that were common to all Departments. If you want a =list of Hazards by Department, this may work:
SELECT h.HazardID
, h.Hazard
, cq.Consequence
, cn.Control
FROM Hazards h
JOIN Consequences cq
ON h.HazardID =3D cq.HazardID
JOIN Conrols cn
ON h.HazardID =3D cn.HazardID
WHERE h.Department =3D <Department>
-- Arnie Rowland
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
<dwightsmail@.gmail.com> wrote in message =news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com...
> > hi all, can anyone help me?
> > I am a relative newbie to sql server and I am more familiar with
> Enterprise Manager than QA. I have made many many access databases
> though. I am making an asp.net application where by there are a set
> number of users, about 80, each one logs in and manages information
> within their department.
> > To get them started a manager has written 10 different hazards that
> will apply to all of the departments, and he has written consequences
> and controls for the hazards. Each department must have this
> information as each will manage and deal with them differently
> > The hazard information is stored in a main 'hazards' table, and the
> consequences and controls are stored in related tables linked by the
> 'hazardID' from the main table to a foreign key 'hazardID' in the
> related tables
> > What i want to know is if there is a relatively simple way of using a
> query to populate the 10 hazards to each department, and to also
> include the related table links, i dont mind renaming the departments
> names to match each hazard, but i do not want to have to relink the
> related tables manually
> > If anyone can give me any advice to get me started i will be =incredibly
> grateful
> > thank you
> > > Table information is below
> > Hazards
> --
> HazardID - identity key field
> Hazard - varchar
> Department - varchar
> > Consequences
> --
> ConsequenceID - identity key field
> HazardID - FK
> Consequence - varchar
> > Controls
> --
> ControlID - identity key field
> HazardID - FK
> Control - varchar
> > > > dwight
>
--=_NextPart_000_00FF_01C6AABD.53A28AE0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
If I understand you correctly, you want =to retreive the Hazard Information and include the Consequences and Controls for =each Hazard, and you want to limit this by Department. But you also indicated =that there were 10 Hazards that were common to all Departments. If you want a =list of Hazards by Department, this may work:
SELECT h.HazardID , h.Hazard , cq.Consequence , =cn.ControlFROM Hazards h JOIN Consequences =cq ON h.HazardID =3D cq.HazardID JOIN Conrols cn ON h.HazardID =3D =cn.HazardIDWHERE h.Department =3D
-- Arnie RowlandMost good =judgment comes from experience. Most experience comes from bad judgment. =- Anonymous
wrote in message news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com=...> => hi all, can anyone help me?> > I am a relative newbie to sql =server and I am more familiar with> Enterprise Manager than QA. I have made =many many access databases> though. I am making an asp.net application =where by there are a set> number of users, about 80, each one logs in and =manages information> within their department.> > To get =them started a manager has written 10 different hazards that> will =apply to all of the departments, and he has written consequences> and =controls for the hazards. Each department must have this> information as each =will manage and deal with them differently> > The hazard =information is stored in a main 'hazards' table, and the> consequences and =controls are stored in related tables linked by the> 'hazardID' from the main =table to a foreign key 'hazardID' in the> related tables> > =What i want to know is if there is a relatively simple way of using a> =query to populate the 10 hazards to each department, and to also> include =the related table links, i dont mind renaming the departments> names =to match each hazard, but i do not want to have to relink the> related =tables manually> > If anyone can give me any advice to get me =started i will be incredibly> grateful> > thank you> => > Table information is below> > Hazards> --> HazardID - identity key field> Hazard - varchar> Department - varchar> > =Consequences> --> ConsequenceID - identity key =field> HazardID - FK> Consequence - varchar> > =Controls> --> ControlID - identity key field> HazardID - FK> Control - varchar> > > > dwight>
--=_NextPart_000_00FF_01C6AABD.53A28AE0--|||Hi Arnie
Thank you for the response
I find it hard to explain things like this :)
What I want to do is to 'append' the hazards, i will manually then go
through them and change the departments so that they are unique for
each department to log in
When I append them I want them to take the new HazardID to the related
tables
So to start with, I have 10 hazards with related consequences and
controls
For an example, I want to repeat those 10 hazards 10 times so that i
can assign them to 10 different departments
When I append the 10 hazards once each for each department I want the
controls and consequences to also append to their tables and for the
HazardID link the tables to the related tables
I know this is repeating data, but it is only to initialise the system
for the users and they will then go on to manage them differently
Does that make sense now?
thank you so very much for your assistance
Arnie Rowland wrote:
> If I understand you correctly, you want to retreive the Hazard Information and include the Consequences and Controls for each Hazard, and you want to limit this by Department. But you also indicated that there were 10 Hazards that were common to all Departments. If you want a list of Hazards by Department, this may work:
>
> SELECT
> h.HazardID
> , h.Hazard
> , cq.Consequence
> , cn.Control
> FROM Hazards h
> JOIN Consequences cq
> ON h.HazardID = cq.HazardID
> JOIN Conrols cn
> ON h.HazardID = cn.HazardID
> WHERE h.Department = <Department>
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <dwightsmail@.gmail.com> wrote in message news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com...
> >
> > hi all, can anyone help me?
> >
> > I am a relative newbie to sql server and I am more familiar with
> > Enterprise Manager than QA. I have made many many access databases
> > though. I am making an asp.net application where by there are a set
> > number of users, about 80, each one logs in and manages information
> > within their department.
> >
> > To get them started a manager has written 10 different hazards that
> > will apply to all of the departments, and he has written consequences
> > and controls for the hazards. Each department must have this
> > information as each will manage and deal with them differently
> >
> > The hazard information is stored in a main 'hazards' table, and the
> > consequences and controls are stored in related tables linked by the
> > 'hazardID' from the main table to a foreign key 'hazardID' in the
> > related tables
> >
> > What i want to know is if there is a relatively simple way of using a
> > query to populate the 10 hazards to each department, and to also
> > include the related table links, i dont mind renaming the departments
> > names to match each hazard, but i do not want to have to relink the
> > related tables manually
> >
> > If anyone can give me any advice to get me started i will be incredibly
> > grateful
> >
> > thank you
> >
> >
> > Table information is below
> >
> > Hazards
> > --
> > HazardID - identity key field
> > Hazard - varchar
> > Department - varchar
> >
> > Consequences
> > --
> > ConsequenceID - identity key field
> > HazardID - FK
> > Consequence - varchar
> >
> > Controls
> > --
> > ControlID - identity key field
> > HazardID - FK
> > Control - varchar
> >
> >
> >
> > dwight
> >
> --=_NextPart_000_00FF_01C6AABD.53A28AE0
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 4045
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> &
>
>
>
>
>
> If I understand you correctly, you want to retreive
> the Hazard Information and include the Consequences and Controls for each
> Hazard, and you want to limit this by Department. But you also indicated that
> there were 10 Hazards that were common to all Departments. If you want a list of
> Hazards by Department, this may work:
>
> SELECT
> h.HazardID ,
> h.Hazard , cq.Consequence , cn.ControlFROM
> Hazards h JOIN Consequences cq
> ON h.HazardID = cq.HazardID JOIN Conrols
> cn ON h.HazardID = cn.HazardIDWHERE
> h.Department =
> -- Arnie RowlandMost good judgment
> comes from experience. Most experience comes from bad judgment. -
> Anonymous
>
>
>< href="http://links.10026.com/?link=mailto:dwightsmail@.gmail.com"> size=2>dwightsmail@.gmail.com> wrote in
> message href="http://links.10026.com/?link=news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com"> face=Arial
> size=2>news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com face=Arial size=2>...> > hi all,
> can anyone help me?> > I am a relative newbie to sql server and I
> am more familiar with> Enterprise Manager than QA. I have made many many
> access databases> though. I am making an asp.net application where by
> there are a set> number of users, about 80, each one logs in and manages
> information> within their department.> > To get them
> started a manager has written 10 different hazards that> will apply to
> all of the departments, and he has written consequences> and controls for
> the hazards. Each department must have this> information as each will
> manage and deal with them differently> > The hazard information is
> stored in a main 'hazards' table, and the> consequences and controls are
> stored in related tables linked by the> 'hazardID' from the main table to
> a foreign key 'hazardID' in the> related tables> > What i
> want to know is if there is a relatively simple way of using a> query to
> populate the 10 hazards to each department, and to also> include the
> related table links, i dont mind renaming the departments> names to match
> each hazard, but i do not want to have to relink the> related tables
> manually> > If anyone can give me any advice to get me started i
> will be incredibly> grateful> > thank you> >
> > Table information is below> > Hazards>
> --> HazardID - identity key field> Hazard -
> varchar> Department - varchar> > Consequences>
> --> ConsequenceID - identity key field>
> HazardID - FK> Consequence - varchar> > Controls>
> --> ControlID - identity key field> HazardID -
> FK> Control - varchar> > > >
> dwight>
> --=_NextPart_000_00FF_01C6AABD.53A28AE0--
I am a relative newbie to sql server and I am more familiar with
Enterprise Manager than QA. I have made many many access databases
though. I am making an asp.net application where by there are a set
number of users, about 80, each one logs in and manages information
within their department.
To get them started a manager has written 10 different hazards that
will apply to all of the departments, and he has written consequences
and controls for the hazards. Each department must have this
information as each will manage and deal with them differently
The hazard information is stored in a main 'hazards' table, and the
consequences and controls are stored in related tables linked by the
'hazardID' from the main table to a foreign key 'hazardID' in the
related tables
What i want to know is if there is a relatively simple way of using a
query to populate the 10 hazards to each department, and to also
include the related table links, i dont mind renaming the departments
names to match each hazard, but i do not want to have to relink the
related tables manually
If anyone can give me any advice to get me started i will be incredibly
grateful
thank you
Table information is below
Hazards
--
HazardID - identity key field
Hazard - varchar
Department - varchar
Consequences
--
ConsequenceID - identity key field
HazardID - FK
Consequence - varchar
Controls
--
ControlID - identity key field
HazardID - FK
Control - varchar
dwightThis is a multi-part message in MIME format.
--=_NextPart_000_00FF_01C6AABD.53A28AE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
If I understand you correctly, you want to retreive the Hazard =Information and include the Consequences and Controls for each Hazard, =and you want to limit this by Department. But you also indicated that =there were 10 Hazards that were common to all Departments. If you want a =list of Hazards by Department, this may work:
SELECT h.HazardID
, h.Hazard
, cq.Consequence
, cn.Control
FROM Hazards h
JOIN Consequences cq
ON h.HazardID =3D cq.HazardID
JOIN Conrols cn
ON h.HazardID =3D cn.HazardID
WHERE h.Department =3D <Department>
-- Arnie Rowland
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
<dwightsmail@.gmail.com> wrote in message =news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com...
> > hi all, can anyone help me?
> > I am a relative newbie to sql server and I am more familiar with
> Enterprise Manager than QA. I have made many many access databases
> though. I am making an asp.net application where by there are a set
> number of users, about 80, each one logs in and manages information
> within their department.
> > To get them started a manager has written 10 different hazards that
> will apply to all of the departments, and he has written consequences
> and controls for the hazards. Each department must have this
> information as each will manage and deal with them differently
> > The hazard information is stored in a main 'hazards' table, and the
> consequences and controls are stored in related tables linked by the
> 'hazardID' from the main table to a foreign key 'hazardID' in the
> related tables
> > What i want to know is if there is a relatively simple way of using a
> query to populate the 10 hazards to each department, and to also
> include the related table links, i dont mind renaming the departments
> names to match each hazard, but i do not want to have to relink the
> related tables manually
> > If anyone can give me any advice to get me started i will be =incredibly
> grateful
> > thank you
> > > Table information is below
> > Hazards
> --
> HazardID - identity key field
> Hazard - varchar
> Department - varchar
> > Consequences
> --
> ConsequenceID - identity key field
> HazardID - FK
> Consequence - varchar
> > Controls
> --
> ControlID - identity key field
> HazardID - FK
> Control - varchar
> > > > dwight
>
--=_NextPart_000_00FF_01C6AABD.53A28AE0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
If I understand you correctly, you want =to retreive the Hazard Information and include the Consequences and Controls for =each Hazard, and you want to limit this by Department. But you also indicated =that there were 10 Hazards that were common to all Departments. If you want a =list of Hazards by Department, this may work:
SELECT h.HazardID , h.Hazard , cq.Consequence , =cn.ControlFROM Hazards h JOIN Consequences =cq ON h.HazardID =3D cq.HazardID JOIN Conrols cn ON h.HazardID =3D =cn.HazardIDWHERE h.Department =3D
-- Arnie RowlandMost good =judgment comes from experience. Most experience comes from bad judgment. =- Anonymous
--=_NextPart_000_00FF_01C6AABD.53A28AE0--|||Hi Arnie
Thank you for the response
I find it hard to explain things like this :)
What I want to do is to 'append' the hazards, i will manually then go
through them and change the departments so that they are unique for
each department to log in
When I append them I want them to take the new HazardID to the related
tables
So to start with, I have 10 hazards with related consequences and
controls
For an example, I want to repeat those 10 hazards 10 times so that i
can assign them to 10 different departments
When I append the 10 hazards once each for each department I want the
controls and consequences to also append to their tables and for the
HazardID link the tables to the related tables
I know this is repeating data, but it is only to initialise the system
for the users and they will then go on to manage them differently
Does that make sense now?
thank you so very much for your assistance
Arnie Rowland wrote:
> If I understand you correctly, you want to retreive the Hazard Information and include the Consequences and Controls for each Hazard, and you want to limit this by Department. But you also indicated that there were 10 Hazards that were common to all Departments. If you want a list of Hazards by Department, this may work:
>
> SELECT
> h.HazardID
> , h.Hazard
> , cq.Consequence
> , cn.Control
> FROM Hazards h
> JOIN Consequences cq
> ON h.HazardID = cq.HazardID
> JOIN Conrols cn
> ON h.HazardID = cn.HazardID
> WHERE h.Department = <Department>
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <dwightsmail@.gmail.com> wrote in message news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com...
> >
> > hi all, can anyone help me?
> >
> > I am a relative newbie to sql server and I am more familiar with
> > Enterprise Manager than QA. I have made many many access databases
> > though. I am making an asp.net application where by there are a set
> > number of users, about 80, each one logs in and manages information
> > within their department.
> >
> > To get them started a manager has written 10 different hazards that
> > will apply to all of the departments, and he has written consequences
> > and controls for the hazards. Each department must have this
> > information as each will manage and deal with them differently
> >
> > The hazard information is stored in a main 'hazards' table, and the
> > consequences and controls are stored in related tables linked by the
> > 'hazardID' from the main table to a foreign key 'hazardID' in the
> > related tables
> >
> > What i want to know is if there is a relatively simple way of using a
> > query to populate the 10 hazards to each department, and to also
> > include the related table links, i dont mind renaming the departments
> > names to match each hazard, but i do not want to have to relink the
> > related tables manually
> >
> > If anyone can give me any advice to get me started i will be incredibly
> > grateful
> >
> > thank you
> >
> >
> > Table information is below
> >
> > Hazards
> > --
> > HazardID - identity key field
> > Hazard - varchar
> > Department - varchar
> >
> > Consequences
> > --
> > ConsequenceID - identity key field
> > HazardID - FK
> > Consequence - varchar
> >
> > Controls
> > --
> > ControlID - identity key field
> > HazardID - FK
> > Control - varchar
> >
> >
> >
> > dwight
> >
> --=_NextPart_000_00FF_01C6AABD.53A28AE0
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 4045
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> &
>
>
>
>
>
> If I understand you correctly, you want to retreive
> the Hazard Information and include the Consequences and Controls for each
> Hazard, and you want to limit this by Department. But you also indicated that
> there were 10 Hazards that were common to all Departments. If you want a list of
> Hazards by Department, this may work:
>
> SELECT
> h.HazardID ,
> h.Hazard , cq.Consequence , cn.ControlFROM
> Hazards h JOIN Consequences cq
> ON h.HazardID = cq.HazardID JOIN Conrols
> cn ON h.HazardID = cn.HazardIDWHERE
> h.Department =
> -- Arnie RowlandMost good judgment
> comes from experience. Most experience comes from bad judgment. -
> Anonymous
>
>
>< href="http://links.10026.com/?link=mailto:dwightsmail@.gmail.com"> size=2>dwightsmail@.gmail.com> wrote in
> message href="http://links.10026.com/?link=news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com"> face=Arial
> size=2>news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com face=Arial size=2>...> > hi all,
> can anyone help me?> > I am a relative newbie to sql server and I
> am more familiar with> Enterprise Manager than QA. I have made many many
> access databases> though. I am making an asp.net application where by
> there are a set> number of users, about 80, each one logs in and manages
> information> within their department.> > To get them
> started a manager has written 10 different hazards that> will apply to
> all of the departments, and he has written consequences> and controls for
> the hazards. Each department must have this> information as each will
> manage and deal with them differently> > The hazard information is
> stored in a main 'hazards' table, and the> consequences and controls are
> stored in related tables linked by the> 'hazardID' from the main table to
> a foreign key 'hazardID' in the> related tables> > What i
> want to know is if there is a relatively simple way of using a> query to
> populate the 10 hazards to each department, and to also> include the
> related table links, i dont mind renaming the departments> names to match
> each hazard, but i do not want to have to relink the> related tables
> manually> > If anyone can give me any advice to get me started i
> will be incredibly> grateful> > thank you> >
> > Table information is below> > Hazards>
> --> HazardID - identity key field> Hazard -
> varchar> Department - varchar> > Consequences>
> --> ConsequenceID - identity key field>
> HazardID - FK> Consequence - varchar> > Controls>
> --> ControlID - identity key field> HazardID -
> FK> Control - varchar> > > >
> dwight>
> --=_NextPart_000_00FF_01C6AABD.53A28AE0--
query to populate child table
hi all, can anyone help me?
INSERT Hazards(HazardID, Hazard, Department)
VALUES (1, 'Fog', 'This Dept')
If there can be more than once consequence per hazard, I think the key
should be (HazardID, ConsequenceNo).
>
INSERT Hazards(HazardID, Hazard, Department)
VALUES (1, 'Fog', 'This Dept')
>
Oh, so this won't work, because you have an IDENITY column, but it
will be a lot easier if you don't have an IDENTITY.
>
>
If there can be more than once consequence per hazard, I think the key
should be (HazardID, ConsequenceNo).
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||(dwightsmail@.gmail.com) writes:
I'm not sure that I understand the question. But it's not that if you
enter a row in Hazards, that there automatically will be a row in
Consequences as consequences. Unless, that is you set up a trigger. But
since the consequence data need more data, that is not really meaningful.
I am a relative newbie to sql server and I am more familiar with
Enterprise Manager than QA. I have made many many access databases
though. I am making an asp.net application where by there are a set
number of users, about 80, each one logs in and manages information
within their department.
To get them started a manager has written 10 different hazards that
will apply to all of the departments, and he has written consequences
and controls for the hazards. Each department must have this
information as each will manage and deal with them differently
The hazard information is stored in a main 'hazards' table, and the
consequences and controls are stored in related tables linked by the
'hazardID' from the main table to a foreign key 'hazardID' in the
related tables
What i want to know is if there is a relatively simple way of using a
query to populate the 10 hazards to each department, and to also
include the related table links, i dont mind renaming the departments
names to match each hazard, but i do not want to have to relink the
related tables manually
If anyone can give me any advice to get me started i will be incredibly
grateful
thank you
Table information is below
Hazards
----
HazardID - identity key field
Hazard - varchar
Department - varchar
Consequences
-------
ConsequenceID - identity key field
HazardID - FK
Consequence - varchar
Controls
----
ControlID - identity key field
HazardID - FK
Control - varchar
dwight(dwightsmail@.gmail.com) writes:
Quote:
Originally Posted by
What i want to know is if there is a relatively simple way of using a
query to populate the 10 hazards to each department, and to also
include the related table links, i dont mind renaming the departments
names to match each hazard, but i do not want to have to relink the
related tables manually
INSERT Hazards(HazardID, Hazard, Department)
VALUES (1, 'Fog', 'This Dept')
Oh, so this won't work, because you have an IDENITY column, but it
will be a lot easier if you don't have an IDENTITY.
Quote:
Originally Posted by
Consequences
-------
ConsequenceID - identity key field
HazardID - FK
Consequence - varchar
If there can be more than once consequence per hazard, I think the key
should be (HazardID, ConsequenceNo).
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I am getting close to understanding that, but how do I take the
consequence and control table information with the hazard insert? Will
the related consequences be inserted with the 'Fog'?
Thank you for helping me
I dont want to have to enter the hazards 80 times if I can learn this
skill
:)
Erland Sommarskog wrote:
Quote:
Originally Posted by
(dwightsmail@.gmail.com) writes:
Quote:
Originally Posted by
What i want to know is if there is a relatively simple way of using a
query to populate the 10 hazards to each department, and to also
include the related table links, i dont mind renaming the departments
names to match each hazard, but i do not want to have to relink the
related tables manually
>
INSERT Hazards(HazardID, Hazard, Department)
VALUES (1, 'Fog', 'This Dept')
>
Oh, so this won't work, because you have an IDENITY column, but it
will be a lot easier if you don't have an IDENTITY.
>
Quote:
Originally Posted by
Consequences
-------
ConsequenceID - identity key field
HazardID - FK
Consequence - varchar
>
If there can be more than once consequence per hazard, I think the key
should be (HazardID, ConsequenceNo).
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||(dwightsmail@.gmail.com) writes:
Quote:
Originally Posted by
I am getting close to understanding that, but how do I take the
consequence and control table information with the hazard insert? Will
the related consequences be inserted with the 'Fog'?
I'm not sure that I understand the question. But it's not that if you
enter a row in Hazards, that there automatically will be a row in
Consequences as consequences. Unless, that is you set up a trigger. But
since the consequence data need more data, that is not really meaningful.
Some people prefer to use the Open Table functionality that is in
Enterprise Manager or SQL Server Management Studio where you can enter
data in a grid. Personally, I find that about slower than typing up a
number of INSERT statments with help of some copy-and-paste skills.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Monday, March 12, 2012
Query to find what is not there
I have a 1:1 relationship between tables and am finding that the parent
is sometimes mising the child.
How do I query for what is not there?
is sometimes mising the child.
How do I query for what is not there?
TIAOn 30 Dec 2004 07:17:36 -0800, srussell705 wrote:
> I have a 1:1 relationship between tables and am finding that the parent
> is sometimes mising the child.
> How do I query for what is not there?
> TIA
SELECT * FROM ParentTbl
WHERE NOT EXISTS (
SELECT * FROM ChildTbl
WHERE ChildTbl.ParentKey = ParentTbl.ParentKey
)
or
SELECT * FROM ParentTbl
WHERE ParentTbl.ParentKey NOT IN (
SELECT ChildTbl.ParentKey FROM ChildTbl)
(The first is probably more efficient)|||
Thanks for teh quick reply.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Here is a wild guess based nothing you told us.
1) Clean up the orphans you have now.
DELETE FROM Children
WHERE NOT EXISTS
(SELECT *
FROM Parents AS P1
WHERE P1.parent_id = Children.parent_id);
2) Get a correct schema that enforces your busines rules with DRI
actions, like this:
CREATE TABLE Parents
(parent_id INTEGER NOT NULL PRIMARY KEY,
...);
CREATE TABLE Children
(parent_id INTEGER NOT NULL
REFERENCES Parents (parent_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
child_id INTEGER NOT NULL,
PRIMARY KEY (parent_id, child_id),
...);
Remeber that is it never enough to mop the floor; you must also fix the
leak.|||"srussell705" <srussell@.lotmate.com> wrote in message
news:1104419856.430687.62340@.c13g2000cwb.googlegro ups.com...
> I have a 1:1 relationship between tables and am finding that the parent
> is sometimes mising the child.
> How do I query for what is not there?
> TIA
Not quite on topic, but I'm always wary of 1:1 relationships. Most of the
places I've seen them, there was such close affinity between the two tables
that they were either the same thing or one could be folded into the other.
Hmm?
Rich
Subscribe to:
Posts (Atom)