Query Syntax: 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>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:</DIV>
> <DIV> </DIV>
> <DIV> SELECT
> h.HazardID &
nbsp; ,
> h.Hazard , cq.Consequence 
, cn.Control FROM
> Hazards h JOIN Consequences cq &n
bsp;
> ON h.HazardID = cq.HazardID JOIN Conrols
> cn ON h.HazardID = c
n.HazardID WHERE
> h.Department = <Department></DIV>
> <DIV> -- Arnie Rowland Most good judgmen
t
> comes from experience. Most experience comes from bad judgment. -
> Anonymous</DIV>
> <DIV> </DIV>
> <DIV> </DIV>
> <DIV><<A
> href="http://links.10026.com/?link=mailto:dwightsmail@.gmail.com"> size=2>dwightsmail@.gmail.com</A>> wrote
in
> message <A
> href="http://links.10026.com/?link=news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com"> face=Arial
> size=2>news:1153284544.918469.266560@.i3g2000cwc.googlegroups.com</A
> face=Arial size=2>...</DIV>> > 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 ma
nages
> 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 contro
ls for
> the hazards. Each department must have this > information as each wi
ll
> manage and deal with them differently > > The hazard informat
ion is
> stored in a main 'hazards' table, and the > consequences and control
s are
> stored in related tables linked by the > 'hazardID' from the main ta
ble to
> a foreign key 'hazardID' in the > related tables > > Wh
at i
> want to know is if there is a relatively simple way of using a > que
ry to
> populate the 10 hazards to each department, and to also > include th
e
> 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 table
s
> manually > > If anyone can give me any advice to get me start
ed 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 &
gt;
> -- > ControlID - identity key field > HazardID -
> FK > Control - varchar > > > >
> dwight ></BODY></HTML>
> --=_NextPart_000_00FF_01C6AABD.53A28AE0--

Tuesday, March 20, 2012

query to populate child tables

No comments:

Post a Comment

Query Syntax

Blog Archive

About Me