Maintaining a one-to-one match on related tables

I have a series of tables already containing data. The first table is a list
of employees. The next is a list of team names.
There is a Primary Key for each employee which relates to a foreign key in
the team names.
The employee table would have
pkid
FirstName
LastName
fkOfficeID
department
The team table would have only
fkEmployeeID
TeamName
Now, I know how to generate a SELECT set with a LEFT JOIN and a search for
NULL in the TeamName to generate a list of employees that do not have a team
name assigned to them, however. Not every employee would have a coresponding
record in the team table, only certain offices have teams.
What is the statement that would:
"INSERT and UPDATE a specific team name record in the team table for
each corresponding record in the employee table for any employee who's
"Office ID" is 5 department is Accounting and is missing a record in the
team table."
I could VB my way through this by
Creating the list of all offices that have teams
Creating the SELECT set mentioned above and whittling it down to
those employees in each office who are in Accounting
IF NOT EXISTS-ing my way through the team list and adding the
appopriate team record
But, is there an easier way? Is it a two step process, one to determine the
missing records in the one-to-one relationship where needed and then do the
team table update?
Julianand the vb programmers here wonder why vb makes me pull my hair out... ;)
insert into team (fkEmployeeID, TeamName)
select e.pkid, 'Office 5 Accounting Team 1'
from employee e
where fkofficeid=5
and department='Accounting'
and not exists (select * from team where fkemployeeid=e.pkid)
not sure what you need to update...
stjulian wrote:
> I have a series of tables already containing data. The first table is a li
st
> of employees. The next is a list of team names.
> There is a Primary Key for each employee which relates to a foreign key in
> the team names.
> The employee table would have
> pkid
> FirstName
> LastName
> fkOfficeID
> department
> The team table would have only
> fkEmployeeID
> TeamName
>
> Now, I know how to generate a SELECT set with a LEFT JOIN and a search for
> NULL in the TeamName to generate a list of employees that do not have a te
am
> name assigned to them, however. Not every employee would have a corespondi
ng
> record in the team table, only certain offices have teams.
> What is the statement that would:
> "INSERT and UPDATE a specific team name record in the team table for
> each corresponding record in the employee table for any employee who's
> "Office ID" is 5 department is Accounting and is missing a record in the
> team table."
> I could VB my way through this by
> Creating the list of all offices that have teams
> Creating the SELECT set mentioned above and whittling it down to
> those employees in each office who are in Accounting
> IF NOT EXISTS-ing my way through the team list and adding the
> appopriate team record
> But, is there an easier way? Is it a two step process, one to determine th
e
> missing records in the one-to-one relationship where needed and then do th
e
> team table update?
> Julian
>
>|||I think like a VB programmer, you know, linearly. What I love about you guys
in SQL is your way of looking at data in 3 dimensions. Folding it on itself
(using table aliases).
Should the line be "from employee AS e" ?
I should take a class on query design.
I'll give it a try tomorrow. Keep an eye on this thread.
Julian
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:eETCybEAGHA.1268@.TK2MSFTNGP11.phx.gbl...
> and the vb programmers here wonder why vb makes me pull my hair out... ;)
> insert into team (fkEmployeeID, TeamName)
> select e.pkid, 'Office 5 Accounting Team 1'
> from employee e
> where fkofficeid=5
> and department='Accounting'
> and not exists (select * from team where fkemployeeid=e.pkid)
>
> not sure what you need to update...
> stjulian wrote:|||Although the issues are 2D, thinking 3D is more impressive. :)
ML
http://milambda.blogspot.com/|||I prefer thinking of it as
VB = "For each x, do this"
SQL = "For all x's, do this"
the AS is optional in aliasing a table or column.
my personal preference is to include AS for column aliases and not for
table aliases.
stjulian wrote:
> I think like a VB programmer, you know, linearly. What I love about you gu
ys
> in SQL is your way of looking at data in 3 dimensions. Folding it on itsel
f
> (using table aliases).
> Should the line be "from employee AS e" ?
> I should take a class on query design.
> I'll give it a try tomorrow. Keep an eye on this thread.
> Julian
>
> "Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
> news:eETCybEAGHA.1268@.TK2MSFTNGP11.phx.gbl...
>