Solved

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

Posted on 2013-06-20
3
372 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 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

730 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