Posted by othellomy on 11/30/06 08:06

Here is one solution:

Delete Appointment a
Where exists (select 'hi there'
From Entity e
Where a.attendee = e. attendee
Group by e. attendee
Having count(e. attendee) <= 1)

You can do the similar step with the intersect table
Appointment_Entity.

Brian Link wrote:
> Panicked, as usual. Who says it's great being a sole proprietor?
>
> Using MS-SQLServer:
>
> 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
> --------------------------
> "The worst thing about censorship is [redacted]"

• Next in forum: Re: Aggregate functions and locking
• Prev in forum: MI5 Persecution: Eye Say, and Lord Gnome Answers
• Thread view: Re: Need Emergency Help with SQL Query (difficulty: moderate)

[Reply to this message]