INSERT INTO joint problem

I'm fairly new to db design, therefore this questions:

I have (among others) three tables:

tblProjects
  (ProjectID, Name, etc.)
tblTeamMembersxProjects (joint table)
  (ProjectID, TeamMemberID)
tblTeamMembers
  (TeamMemberID, FirstName, LastName, etc.)

Now, I have an asp page where one can add/edit project information. That page has a select list included that shows ALL possible TeamMembers pulled from tblTeamMembers (if it's an update then the TeamMembers associated with the project are already highlighted). Right now, if one enters a NEW project and selects some TeamMembers on that list I am running two separate conn.execute(sql) commands: one for the insertion of the project and the other to insert the TeamMembers from the list into the joint table.

This solution sounds like a fairly dirty solution to me. Is it possible to utilize the JOIN command in INSERT & UPDATE commands...???

I tried to find info on other sites (such as sql-zone.com or informit.com) but have not found anything concerning that specific question...

Thanx a bundle!
LVL 1
gsnAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
Insert and Update statements will always only insert/update rows in 1 table, but the inserted/new values may come from a join.
INSERT yourtable (columns)
SELECT columns
FROM Table1 JOIN Table2 ON condition
UPDATE yourtable
SET column1 = value1, column2= value2...
FROM yourtable JOIN othertable ON condition

Is this what you need ?
0
 
chsohCommented:
I still not understand what is

Is it possible to utilize the JOIN command in INSERT & UPDATE commands?

you mean you want to INSERT & UPDATE perform is single SQL statement ? From your question, are you facing problem when ADD a new projects only ? or Add & UPDATE projects ??
0
 
gsnAuthor Commented:
yes, I want to INSERT & UPDATE in a single SQL statement, IF POSSIBLE... As I said: right now I am executing two separate sql statements (for both cases: INSERT and UPDATE!)
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
chsohCommented:
The INSERT, UPDATE & DELETE statement can only be execute for a single table. so there is no way to do the statement like

INSERT into projects, TeamMembersxProjects
VALUES .......

only SELECT statement can do the query on multiple tables like

SELECT * from Projects A, TeamMembersxProjects B, TeamMembers C
where A.projectID = "11111"
AND A.projectID = B.projectID
and B.TeamMemberID = C.TeamMemberID
0
 
srikant033100Commented:
if u want to execute any mutiple query and DML operation along with some validation then u can go for procedure or functions depending on your requirement where u can even do some validation like a program. i think this will solve ur problem of else give me exactly what u want with an example so that i will try to solve the problem back with an example.

Bye

Srikant
0
 
gsnAuthor Commented:
angelIII brought me on the right track... sorry it took so long...
0
 
gsnAuthor Commented:
That were an easy 100 points - I appreciate your help!
0
 
srikant033100Commented:
Hi

I don't understand on what ground u have rejected my answer. The question what u gave what that u want to insert or update some records based on certain condition or criteria. The procedure or function is the best possible way. Even what Mr. qsn gave was same and the if we write it in a procedure that would be much more helpfull So can u give me the ground on which u have rejected the answer.
0
 
chsohCommented:
I don't understand on what ground u have accepted angelIII answer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.