• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 536
  • Last Modified:

self join or get last value from table

I've one table.
CREATE TABLE dba.t_event(plan_event_id int,event_id int,price_code varchar(10))
--PRIMARY KEY ON ALL 3 COLUMNS COMBINED. HERE ARE THE VALUES.
INSERT INTO VALUES( 928,914,'A')
INSERT INTO VALUES( 928,914,'AC')
INSERT INTO VALUES( 928,900,'B')
So for same plan_event_id we have multiple combinations of event_id and price_code
There is a trigger on this table and it checks when we delete last plan_event_id from t_event then only delete corresponding record from other table.
So now for above table,
DELETE FROM dba.t_event WHERE plan_event_id =928, event_id = 900 AND price_code = 'B'-- DO Nothing
DELETE FROM dba.t_event WHERE plan_event_id =928, event_id = 914 AND price_code = 'AC'-- DO Nothing
DELETE FROM dba.t_event WHERE plan_event_id =928, event_id = 914 AND price_code = 'A'-- Now here the trigger should check if this is the last plan_event_id?? if yes then only
delete corresponding record from other table. Looks like I've to write self join or use MAX function. Below if what i tried
but not working
IF NOT EXISTS ( SELECT *
                          FROM deleted
                            JOIN ( SELECT MAX(plan_event_id)
                                     FROM dba.t_event
                                    WHERE deleted.plan_event_id  = dba.t_event.plan_event_id
                                     GROUP BY plan_event_id)
        BEGIN
          DELETE FROM Other Table
        END
0
cottage125
Asked:
cottage125
  • 2
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you want to read up this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
this should help to formulate the sql, including the delete(s)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
rereading the question, please ignore the above.
I think it will be rather like this (as part of the delete trigger):
DELETE o
  FROM [Other Table] o
  WHERE o.plan_event_id in ( 
        SELECT d.plan_event_id
           FROM DELETED d
          WHERE NOT EXISTS( SELECT NULL FROM  dba.t_event e WHERE e.plan_event_id = d.plan_event_id )
 )

Open in new window

0
 
Chris LuttrellSenior Database ArchitectCommented:
Here is a delete trigger against your test code that will work.
I use the EXCEPT operator to perform set logic to find the list of IDs (if any) to delete from the other table.  Since you have an primary key (index) that starts with that id it will be very efficient.
CREATE TABLE t_event(plan_event_id int,event_id int,price_code varchar(10),
--PRIMARY KEY ON ALL 3 COLUMNS COMBINED. HERE ARE THE VALUES.
CONSTRAINT PK_t_event PRIMARY KEY CLUSTERED (plan_event_id, event_id, price_code));
CREATE TABLE other (plan_event_id INT);

INSERT INTO t_event VALUES( 928,914,'A')
INSERT INTO t_event VALUES( 928,914,'AC')
INSERT INTO t_event VALUES( 928,900,'B')
INSERT INTO other VALUES ( 928 ) -- just to get this value in other table for now
GO
--So for same plan_event_id we have multiple combinations of event_id and price_code
--There is a trigger on this table and it checks when we delete last plan_event_id from t_event then only delete corresponding record from other table.
CREATE TRIGGER trg_t_event_remove_from_other_on_last_id
ON t_event
AFTER DELETE
AS
DELETE FROM other WHERE plan_event_id IN
(
SELECT plan_event_id
FROM DELETED 
EXCEPT
SELECT plan_event_id
FROM t_event)
GO


--So now for above table delete and see what happens in other table
DELETE FROM t_event WHERE plan_event_id =928 AND event_id = 900 AND price_code = 'B'-- DO Nothing
SELECT plan_event_id FROM other; -- still there
DELETE FROM t_event WHERE plan_event_id =928 AND event_id = 914 AND price_code = 'AC'-- DO Nothing
SELECT plan_event_id FROM other; -- still there
DELETE FROM t_event WHERE plan_event_id =928 AND event_id = 914 AND price_code = 'A'-- Now here the trigger should check if this is the last plan_event_id?? if yes then only 
SELECT plan_event_id FROM other; -- Gone!

--Clean up code, uncomment for testing, 
--commented out for now to avoid problems on real tables
--DROP TABLE other;  
--DROP TABLE t_event;

Open in new window

Results:
Results
0
 
Scott PletcherSenior DBACommented:
DELETE FROM ot
FROM dbo.OtherTable ot
INNER JOIN (
    SELECT d.plan_event_id
    FROM (
        SELECT DISTINCT plan_event_id
        FROM deleted
    ) AS d
    WHERE
        NOT EXISTS (
            SELECT 1
            FROM dbo.t_event e            
            WHERE
                e.plan_event_id = d.plan_event_id
        )
) AS plan_event_ids_removed ON
    plan_event_ids_removed.plan_event_id = ot.plan_event_id
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now