Check 3 different 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

SQL Server Development (2000)

Check 3 different tables?

Author

Topic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-02-04 : 17:43:06

I am trying to insert data, but before I do that I need to check to see if that case number exists in three different tables. And if it does, I don't want it to import. How do I do that? This is what I have now, but of course it doesn't work.


INSERT INTO tblAccurint_Above1500 (acctno) VALUES ('011-222222')
WHERE NOT EXISTS 
(SELECT tblFeePaid.CaseNumber,tblHudList.CaseNumber,tblCapRec.CaseNumber
 FROM tblFeePaid AS tfp Left Join tblHUDList AS thl Left Join tblCapRec AS tcr
 WHERE tfp.CaseNumber = thl.CaseNumber = tcr.CaseNumber)

Thanks!

Brenda

If it weren't for you guys, where would I be?

chadmat
The Chadinator

1974 Posts

Posted - 2005-02-04 : 18:00:47

[code]
DECLARE @Count INT
SET @Count = 0

SELECT @Count = @Count + COUNT(*) FROM tblFeePaid WHERE CaseNumber = '011-222222'
SELECT @Count = @Count + COUNT(*) FROM tblHUDList WHERE CaseNumber = '011-222222'
SELECT @Count = @Count + COUNT(*) FROM tblCapRec WHERE CaseNumber = '011-222222'

IF (@Count = 0)
BEGIN
INSERT INTO tblAccurint_Above1500 (acctno) VALUES ('011-222222')
END
[/code]

There may be a way to do it in 1 statement, but since I don't know the full requirements, or any details of the system, I have given a multi step solution.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-02-04 : 18:10:40

Perfect! Thanks! Sometimes I wish I thought of things, but I just don't sometimes. In time I guess.

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-04 : 18:41:19

Shouldn't referential integrity be placed on your database to prevent this?

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-02-04 : 18:44:02

Well, I am inserting data into the main table, but I need to make sure it is not in two other tables. I have a primary key on the main table, but just want to make sure it is not in two other tables. Is that wrong?

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-04 : 18:45:37

So you don't have foreign keys?

Tara

jon3k
Starting Member

49 Posts

Posted - 2005-02-04 : 18:47:37

If you have to check 3 tables for a piece of info, then I assume you're storing this info in 3 places.

It sounds to me like this database isn't properly normalized, or you wouldn't be running into this problem.

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-02-04 : 18:47:48

nope. really don't know much about those. I should research that huh?

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-04 : 18:49:52

Yep. I would research normalization and referential integrity. These are fundamental to proper database design.

Tara

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2005-02-06 : 09:55:26

Tara is right on the money.
You would be far better off reading up
on keys and constraints to fix this problem
rather than changing your query.
Don't fix it with a sledgehammer when a nutcracker will work!
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2005-02-07 : 13:03:12

Thanks Tara! I remember learning about normalization and referential integrity in school. I just haven't been practicing it since I learned it. I re-studied it a little bit this weekend, plus for the past few hours since I've been at work. Now I just need to fix some things on the database. Thanks for the help!

Brenda

If it weren't for you guys, where would I be?

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-07 : 13:27:23

quote:
Originally posted by chadmat

DECLARE @Count INT
SET @Count = 0

SELECT @Count = @Count + COUNT(*) FROM tblFeePaid WHERE CaseNumber = '011-222222'
SELECT @Count = @Count + COUNT(*) FROM tblHUDList WHERE CaseNumber = '011-222222'
SELECT @Count = @Count + COUNT(*) FROM tblCapRec WHERE CaseNumber = '011-222222'

IF (@Count = 0)
BEGIN
INSERT INTO tblAccurint_Above1500 (acctno) VALUES ('011-222222')
END

There may be a way to do it in 1 statement, but since I don't know the full requirements, or any details of the system, I have given a multi step solution.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

So you're saying the answer Chad gave is incorrect? How much more could this be improved, Tara?

Semper fi,

Xerxes, USMC (Ret)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-07 : 13:30:05

I never said it was incorrect.

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-07 : 14:01:27

I apologize, Tara, I misspoke. I meant to ask that if you believed that Chad's solution could've been better, then how? I guess when you mentioned foreign keys I thought you would elaborate.

Thanks

Semper fi,

Xerxes, USMC (Ret)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-07 : 14:07:34

Chad's solution addresses Brenda's problem, plain and simple. Our comments about foreign keys and referential integrity have to do with her database design. She should only have to look at the parent table to see if she needs to insert it. She should not have to check the child tables as well. If foreign keys were in place, then you'd know no child data exists when the parent record doesn't exist.

Tara

chadmat
The Chadinator

1974 Posts

Posted - 2005-02-07 : 14:39:25

Tara's suggestions are better than my solution. You might consider my solution just a solution to syptoms of a deeper rooted problem. Her suggestion is to treat the root problem, and not the symptoms.

She is right. I didn't think about the question enough to realize that, and I just answered the question posted (Without thinking, Why would you need to do that).

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-07 : 14:47:20

What?

Now we're giving answers that are too complete?

That'll never be on the cert exam...

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources