One to many tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

One to many tables

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-12-08 : 10:13:00

Can someone give me the SQL for an sp which will check whether there are any records in my many table and delete the record from the one table if there aren't any.
My tables are called tblRequests (one) and tblRequestDetails(Many). They are joined on MovementId

Many thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-08 : 10:22:37

Okay, this is the hint:

Select * 
from tblRequests r
LEFT JOIN tblRequestDetails d
on r.MovementId = d.MovementId
Where d.MovementId Is NULL

Now, try to convert this into DELETE statement!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-08 : 10:46:17

You can also use not exists

Delete R from tblRequests R
where not exists
(select * from tblRequestDetails where MovementId = R.MovementId)

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-08 : 10:53:45

Hey, Madhivanan!

You lost all the excitement about how he is going to convert that to DELETE !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-08 : 10:59:38

quote:
Originally posted by harsh_athalye

Hey, Madhivanan!

You lost all the excitement about how he is going to convert that to DELETE !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Still You have chance to see that. My suggestion is different from yours anyway

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-08 : 11:09:56

No Chance!!

I bet he will simply take your solution...(Well, he should take because yours may be bit more efficient than mine) !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources