SQL Problem for ICT Coursework

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

General SQL Server Forums

New to SQL Server Programming

SQL Problem for ICT Coursework

Author

Topic

Fribble
Starting Member

3 Posts

Posted - 2007-10-17 : 17:11:21

Hi

I have a small problem I have run into with my coursework for ICT. I have a database for a garage, with a Customer Table and a Car Table in it. I wish to have a delete query, that will remove the customer from the table, and remove all the cars that are under his ID number. So far I have;

DELETE tblCustomer.*
FROM tblCustomer
WHERE (tblCustomer.[Customer ID])=[Enter Customer ID];

and

DELETE tblCar.*
FROM tblCar
WHERE (((tblCar.Owner)=[Enter Customer ID]));

(Customer ID in car table is named Owner)

So i need to lookup a customer with ID X then delete it, then find all the cars with owner of X, then delete all those too.

Is there anyway to do this in one statment, where it will not ask for a "Enter Customer ID" input twice.

Many thanks in advance

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-10-17 : 17:19:13

That looks like you are using MS Access, not SQL Server.

If it is Access, you should post you question in the Access forum.

Or ask you teacher for help.

CODO ERGO SUM

Fribble
Starting Member

3 Posts

Posted - 2007-10-17 : 17:20:40

ok, thanks.

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-10-17 : 17:26:59

Fribble:

I have learned in the few days in this forum, that people here are not very helpful. They don't give you an example or give you a streight and clear down to the point answer.
Here is what you need to do, Good Luck and maybe you can help me next time.

Delete Customer
From dbo.Customer a
Inner Join dbo.Car b
WHERE a.Customer =b.Customer

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-17 : 17:30:04

quote:
Originally posted by osirisa

I have learned in the few days that I been in this forum, that this people are not very helpful. They don't give you an example or give you a streight forward answer.

Posts like that will get your account locked. If you don't want our help, then feel free to stop posting new threads.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-10-17 : 17:34:29

Why Tkizer? Do you think the previous answer from Michael was helpful? I am looking for help, you are posting yourself as an SQL Team, I just trying to get clear help, just like the one I have give to this person. I don't want to start an argument, but I just have been honest.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-17 : 17:37:30

Because your post was very rude and uncalled for. I'm a moderator here and will be keeping the peace in these forums.

Michael is simply pointing out that the thread starter did not post the question in the correct forum. This forum is for SQL Server. We have an Access forum here too, so Access questions should go in that forum. His response about asking the instructor reflects the policies here of not doing other people's homework.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-17 : 17:43:09

osirisa...that would delete from a table called Customer. I don't see how it would delete from the 2 tables he's talking about. I don't believe you can make a single delete statement delete from 2 tables. Cascading deletes would work though.

One thing you need to understand is that everyone here does this voluntarily. We all work 40+ hr a week jobs and do this as we have time on our own will. No one gets paid here...except when your question(s) get answered and your work gets easier and you do better and look good and YOU get that big fat raise (hence you get paid)...because of posters here helping you...as they have time. So show some respect or cough up some $$$ for our expertise.

Fribble, have a look at Macros in Access. Since you want to delete from 2 tables in the click of a button, a macro may be needed. You could have a table with a single row in it for the CustomerID/OwnerID that needs to be deleted. You could have a form built in Access that brings up that table and one record. You could then use that table in your delete query(s). With a macro you can tell it to run both queries. So when they click OK after entering the ID, the OK button runs the macro which closes the form, runs the 2 delete queries which use the record just entered from the form.

Another option would be to do the whole thing with VB code inside of Access (Modules I think) but I don't know much about doing it that way.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-10-17 : 18:06:54

quote:
Originally posted by osirisa

Why Tkizer? Do you think the previous answer from Michael was helpful? I am looking for help, you are posting yourself as an SQL Team, I just trying to get clear help, just like the one I have give to this person. I don't want to start an argument, but I just have been honest.

I think it was helpful to tell him to post his question in a forum where questions like that might be answered. I am not an Access expert, so I can't really help him with it.

Also, your answer was not very helpful since it was just flat out wrong. Either you misunderstood the question, or just don't know what you are talking about.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources