?
Solved

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

Posted on 2013-06-20
3
Medium Priority
?
377 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
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:
Kent Olsen earned 1500 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup
Suggested Courses

770 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