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]"Brian Link wrote:
Quote:
Originally Posted by
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.
If deletions from A_Appointment cascade to AX_AppointmentEntry:
delete from A_Appointment
where AppointmentKey in (
select AppointmentKey
from AX_Appointment_Entity
group by AppointmentKey
having count(*) <= 1
)
If deletions from A_Appointment are blocked until the matches in
AX_Appointment_Entity are also deleted:
select AppointmentKey
into #appointments_to_delete
from AX_Appointment_Entity
group by AppointmentKey
having count(*) <= 1
delete from AX_Appointment_Entity
where AppointmentKey in (
select AppointmentKey
from #appointments_to_delete
)
delete from A_Appointments
where AppointmentKey in (
select AppointmentKey
from #appointments_to_delete
)
If deletions from A_Appointment leave orphans in AX_Appointment_Entity
(why?):
delete from A_Appointment
where AppointmentKey in (
select AppointmentKey
from AX_Appointment_Entity
group by AppointmentKey
having count(*) <= 1
)
delete from Ax_Appointment_Entity
where AppointmentKey in (
select AppointmentKey
from AX_Appointment_Entity
group by AppointmentKey
having count(*) <= 1
)|||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:
Quote:
Originally Posted by
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]"
Quote:
Originally Posted by
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)
A_Appointment doesn't have an 'attendee' column. Original post: "One
appointment may have multiple attendees (Appointment_Entity)".
Reworking this to use AX_Appointment_Entity (and to change 'delete' to
'delete from') ought to lead to another valid solution.|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Guessing at what you meant to post, and using data
element names that make sense, does your schema look like this?
CREATE TABLE Appointments
(appt_id INTEGER NOT NULL PRIMARY KEY,
appt_date DATETIME NOT NULL,
...);
CREATE TABLE Attendees
(attendee_id INTEGER NOT NULL PRIMARY KEY,
attendee_name VARCHAR(20) NOT NULL,
..);
CREATE TABLE Attendance
(appt_id INTEGER NOT NULL
REFERENCES Appointments (appt_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
attendee_id INTEGER NOT NULL
REFERENCES Attendees (attendee_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (appt_id, attendee_id));
1)You never use "-key" as a suffix. That tells us HOW the data
element is used and not WHAT the data element is. Likewise, you never
put silly prefixes that tell WHERE there is an appearance or what the
data type used is.
2)The name "entity" is too vague to be data element name. Do you
also use "thingie" or "Whatyamacallit" in your data dictionary?
Use particular names, not vague ones. If you cannot thik of a good
name, then you do not have a clear idea of what you are doing yet.
3)A table models a set of like entities and therefore should have a
collective or plural name. Unless they really do model one thing.
4)The correct term is a relationship table, in this case a
many-to-many relationship. There is not such thing as an intersection
or association table.
5)Notice that you do most of your work with DRI actions, not in code.
The idea of a RDBMS is that it keeps data integrity for you.
Quote:
Originally Posted by
Quote:
Originally Posted by
>My task is to delete all appointments and related Attendance rows where the number of attendees is one or less. <<
DELETE FROM Appointments
WHERE appt_id
IN (SELECT A1.appt_id
FROM Attendance AS A1
GROUP BY A1.appt_id
HAVING COUNT(*) <= 1);
The DRI makes < 1 redundant, but it preserves what you posted. The DRI
actions will re-arrange the Attendance as people or appointments drop
out.|||--CELKO-- wrote:
Quote:
Originally Posted by
1)You never use "-key" as a suffix. That tells us HOW the data
element is used and not WHAT the data element is.
I work with an established package that uses "-key" as a suffix
regularly, but always for artificial keys (so "what the data element
is" is established across the board by convention as "nothing the
end user has any business seeing").
Quote:
Originally Posted by
5)Notice that you do most of your work with DRI actions, not in code.
The idea of a RDBMS is that it keeps data integrity for you.
[snip]
Quote:
Originally Posted by
The DRI makes < 1 redundant, but it preserves what you posted. The DRI
actions will re-arrange the Attendance as people or appointments drop
out.
If they drop out completely. If a person drops one appointment but
retains others, then the system should directly delete the Attendance
row corresponding to the dropped person+appointment pair.|||You have three possible actions which should be in separate procedures
CREATE PROCEDURE DelAppointment (@.myappt_id INTEGER)
DELETE FROM Appointments -- meeting cancelled
WHERE appt_id = @.my_appt_id;
CREATE PROCEDURE DelAttendee(@.my_attendee_id INTEGER)
DELETE FROM Attendees -- employee gone
WHERE attendee_id = @.my_attendee_id;
CREATE PROCEDURE DelAttendance
(@.my_attendee_id INTEGER , @.myappt_id INTEGER)
BEGIN
DELETE FROM Attendance -- employee removed from meeting
WHERE appt_id = @.my_appt_id
AND attendee_id = @.my_attendee_id;
-- if the was the next to last guy, cancel the meeting
DELETE FROM Appointments
WHERE appt_id
IN (SELECT A1.appt_id
FROM Attendance AS A1
GROUP BY A1.appt_id
HAVING COUNT(*) <= 1);
END;
CREATE PROCEDURE
(@.my_attendee_id INTEGER , @.myappt_id INTEGER)
Watch your error handling and transaction level in the last procedure.
