Emergency query help!! - 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)

Emergency query help!!

Author

Topic

BrianLink
Starting Member

1 Post

Posted - 2006-11-29 : 23:37:08

I'm self-employed as a .NET programmer. My knowledge of SQL is rudimentary enough to allow me to make stored procs of simple select, delete and update queries. This is puzzling me though:

Panicked, as usual. Who says it's great being a sole proprietor?

Three tables: A_Appointment, AX_Appointment_Entity and E_Entity. AX_Appointment_Entity is an intersect/association table between A_Appointment and E_Entity. One appointment may have many attendees (Appointment_Entity). One attendee (Entity) may have many appointments.

My task is to delete all appointments and related AX_Appointment_Entity rows where the number of attendees is one or less.

I'm great at standard select or delete queries. Because this straddles tables in a way I'm less skilled in, I'm pleading for help.

Thanks for any ideas. fwiw, I'm using Transact-SQL.

BLink

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-29 : 23:48:41

[code]
declare @a table ( Appointment int not null )

insert into @a
select
a.Appointment
from
A_Appointment a
left join
AX_Appointment_Entity b
on a.Appointment = b.Appointment
group by
a.Appointment
having
count(*) = 1

delete from AX_Appointment_Entity
where
Appointment in
( select Appointment from @a )

delete from A_Appointment
where
Appointment in
( select Appointment from @a )
[/code]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources