Query Syntax: newbie

Showing posts with label newbie. Show all posts

Friday, March 30, 2012

query, special transpose and merge of data

hi all,

i'm a newbie with a big problem :). I want to create a query, which creates data in a merged and transposed way.

i have 3 tables, but only two of them are interesting for me now.

Table0:
F_id, prop1, prop2 ...
-------
10, x, y
20, v, d

Table1 (m:n Table, connecting Table0 with Table 2):
F_id, V_id
----
10, a
10, b
20, a

Table2:
V_id
--
a
b
c
d

the sql should create this result:
SQL Result:
F_id, a, b, c, d
-----
10, 1, 1, 0, 0
20, 1, 0, 0, 0

the tupels of Tabl2 with V_id should be transposed as columns names of the sql result.
Then every entry in the Table1 (m:n-Table) should insert a '1' in the column of the corresponding V_Id, otherwise if there is no connection between Table0 and Table2 in the m:n-Table, then there is a '0' to be inserted.

In the moment i have no clue, i read a lot about transposing and crosstab things, but that was no help for my special problem.

I appreciate any help.
Thanks!select T0.F_id
, sum(case when T1.V_id = a
then 1 else 0 end) as a
, sum(case when T1.V_id = b
then 1 else 0 end) as b
, sum(case when T1.V_id = c
then 1 else 0 end) as c
, sum(case when T1.V_id = d
then 1 else 0 end) as d
from Table0 as T0
inner
join Table1 as T1
on T1.F_id = T0.F_id
group
by T0.F_id|||Thanks. But the problem is that the Table1-entries are dynamic or the number of entries are variable.

Maybe i correct sth; its not really important to get all of these Table1-entries, but all entries from the m:n Table should be inserted.|||thanks, but you should have mentioned that in your initial post

would have saved me wasting my time writing sql that you can't use

:)|||Besides, this looks like a clasical "homework" assignment -- what have you done yourself to solve the problem? :mad:|||lkbrown, if the number of entries is variable, then this problem cannot be done with just sql

which is probably why he was posting|||if the number of entries is variable, then this problem cannot be done with just sql
It can, by using recursive SQL.
(Of course, one cannot return a "variable" number of columns, but a column can be returned which contains a variable amount of concatenations of expressions.)|||It can, by using recursive SQL.oh, please do show an example

and please make sure it is standard sql, not db2 or something proprietary

:)|||oh, please do show an example

and please make sure it is standard sql, not db2 or something proprietary
WITH T(F_id, aux, V_id) AS
( SELECT Table0.F_id,
MIN(Table1.V_id),
COALESCE(T.V_id, '') || ', ' || MIN(T1.V_id)
FROM Table0 AS T0 LEFT OUTER JOIN T ON T0.F_id = T.F_id
INNER JOIN Table1 AS T1 ON T0.F_id = T1.F_id
WHERE T.aux IS NULL or T1.V_id > T.aux
GROUP BY Table0.F_id
)
SELECT F_id, V_id
FROM T

Didn't test it, so there could be some minor tweaks ...)|||that's mighty impressive, i like it

but frankly, i get lost when i try to understand what it's doing

:)|||thanks a lot.
i tried to get it work, even though i didnt get it completely. I need a little time for it.|||I've tested the following and it works:
create table T0 ( f int ) ;
create table T1 ( f int , v char(1) ) ;
insert into T0(f) values(10) union all values(20) ;
insert into T1(f,v) values(10,'a') union all values(10,'b') ;
insert into T1(f,v) values(20,'b') union values(20,'c') union values(20,'d');

with T (f, v, aux) AS
(SELECT f, CAST('' AS varchar(255)), CAST(null AS varchar(255)) FROM T0
UNION ALL
SELECT T.f, T.v||', '||coalesce(T1.v, ''), coalesce(T1.v, '')
FROM T, T1
WHERE T.f = T1.f AND coalesce(T.aux, '') < T1.v
)
SELECT f, substr(v, 3)
FROM T AS Tx
WHERE length(v) = (SELECT max(length(v))
FROM T
WHERE T.f = Tx.f)
Quick explanation:
The "recursive" table T is built up as follows:
- First it's given all rows of table T0, i.e.
10, '', ''
20, '', ''
- Then the join of this table with T1 is added. The result is
10, '', ''
20, '', ''
10, ', a', 'a'
10, ', b', 'b'
20, ', b', 'b'
20, ', c', 'c'
20, ', d', 'd'
- This last step is iterated, but such that only rows of T and T1 are considered to be joined if T.aux (last column) is strictly smaller than T1.v .
Hence the following rows are added to T in step 3:
10, ', a, b', 'b'
20, ', b, c', 'c'
20, ', b, d', 'd'
20, ', c, d', 'd'
Finally (for the small tables used here) the row
20, ', b, c, d', 'd'
is added.
With this table T, the actual query (SELECT f, substr(v, 3) FROM T) is executed. The "substring" removes the leading ", " while the "WHERE" condition only keeps the longest strings in v, per f, i.e. the result is:
t | v
-- + ---
10 | a, b
20 | b, c, d|||So Sorry, that i didnt thanked you!
Thanks a lot Peter!!!! This was helping me out!!!!!!

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>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--

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--

query to populate child table

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(dwightsmail@.gmail.com) writes:

Quote:

Originally Posted by

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

>
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.

Friday, March 30, 2012

query, special transpose and merge of data

Tuesday, March 20, 2012

query to populate child tables

query to populate child tables

query to populate child table

Query Syntax

Blog Archive

About Me