Solved

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

Posted on 2013-06-20
3
367 Views
Last Modified: 2013-06-21
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

0
Comment
Question by:ditallop
  • 2
3 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 39264017
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
 

Author Comment

by:ditallop
ID: 39264174
@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
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 39264193
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

803 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