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

Paula DiTalloIntegration developerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
Oh.  Ok.

Since the description doesn't parse the source, that's applicable here, too.

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

If you want to parse the source (FROM clause), it's a logical AND of the rows in the two tables based on the join key.

Sorry, but I'm not familiar with the syntax.

Kent.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
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.
0
 
Paula DiTalloIntegration developerAuthor Commented:
@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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.