Solved

self join or get last value from table

Posted on 2013-01-25
4
502 Views
Last Modified: 2013-01-31
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
Comment
Question by:cottage125
  • 2
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38820310
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
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 167 total points
ID: 38820323
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
 
LVL 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 166 total points
ID: 38820522
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 167 total points
ID: 38820543
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now