asked on

SQL: How do I best explain in mathematical terms what this does?

Techies--

I want to be able to express how I've matched data from 2 separate tables; reporting on the action in a single line in mathematical terms-- its been 20+ years since I've been in a formal classroom setting, so I don't want to give misinformation.

Briefly, the event_header describes charge back events. Let's say I am only interested in reporting that the acknowledgement of the charge backs for a general event have come back; detail reconciliation is unnecessary--and not really feasible without the event_detail table.

In a nutshell, here's the sql which brings back the single row:

 select distinct
        h.eventid,
        h.totalvalue
   from event_header h
    inner join event_verify v
     on h.fkcol1 = v.fkcol1
     and h.fkcol2 = v.fkcol2
     and h.TotalValue = v.TotalValue;

Open in new window

Here's the sql that brings back the actual header event and the verification information as they exist in both tables:

  select *
   from event_header h
    inner join event_verify v
     on h.fkcol1 = v.fkcol1
     and h.fkcol2 = v.fkcol2
     and h.TotalValue = v.TotalValue;

Open in new window

Here's the build script...

drop table event_header;
drop table event_verify;

create table event_header
 (eventID int identity (1,1),
  eventType varchar(40),
  fkcol1 int,
  fkcol2 int,
  TotalValue decimal(10,2));
 
 
  create table event_verify
  (verifyEventId int identity (100,1),
   SubEventType varchar(40),
   fkcol1 int,
   fkcol2 int,
   TotalValue decimal(10,2),
   SubVal_1 decimal(6,2));
   
 
 insert into event_header
 (eventType,
  fkcol1 ,
  fkcol2 ,
  TotalValue)
 values
 ('ResourceChargeback',
  100,
  1000,
  5000.00);
 
  insert into event_verify
  ( SubEventType,
   fkcol1 ,
   fkcol2 ,
   TotalValue ,
   SubVal_1)
  values
  ('BackupDB',
   100,
   1000,
   5000.00,
   2500.00);
   
   
 insert into event_verify
 ( SubEventType,
   fkcol1 ,
   fkcol2 ,
   TotalValue ,
   SubVal_1)
 values
  ('ExecuteScript',
   100,
   1000,
   5000.00,
   2500.00);
   
   
 select * from event_header;
 select * from  event_verify;

Open in new window

Kent Olsen

Hmm.....

Not sure quite how you want to describe this mathematically, but the first thing that comes to mind is set theory.

select *
from event_header h
inner join event_verify v
on h.fkcol1 = v.fkcol1
and h.fkcol2 = v.fkcol2
and h.TotalValue = v.TotalValue;

event_header represents all event_headers.
evert_verify represents all event_verify items.
The query result is the set of all event_headers with at least one event_verify item.

Paula DiTallo

ASKER

@Kdo,

I had something more like this in mind

.. using this simple example with no subqueries in the where-condition:

SELECT Select-list
FROM R1, . . . , R2 T2, . . .
WHERE Where-condition

translates to this in relational algebra:

legend:
_projection_ = projection symbol
_selection_ = selection symbol

_projection_ Select-list _selection_ Where-condition(R1x...x _selection_T2(R2) x...)

... sorry, I can't figure out how generate the greek symbols here.

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial