Introduction
I had a requirement in my application to retrieve mutually exclusive or disjoint sets of data into our system. Two sets A and B are said to be mutually exclusive or disjoint if they have nothing in common. Our system later would execute these queries to retrieve disjoint data.
Background
Data Retrieval and Manipulation in SQL
There are three different but related forms of relational calculus: tuple calculus, domain calculus, and transform languages. SQL is based on the transform language SEQUEL.
Commands in SQL specify in a specific syntax which columns to manipulate, from what tables, and for what rows.
Basic Data Retrieval and Manipulation in SQL
SQL data retrieval statements include the following three distinct clauses:
SELECT |
Lists the columns (including expressions involving columns) from base tables or views to be projected into the tables that will be the result of the command. |
FROM |
Identifies the tables or views from which columns will be chosen to appear in the result table, and includes the tables or views needed to join tables to process the query. |
WHERE |
Includes the conditions for row selection within a single table or view, and the conditions between tables or views for joining. |
Before getting into the details, let me illustrate it with a simple example. Suppose that we have the following database:
There are a few remarks to make about it:
- All tables have primary keys.
PAYMENT_DETAIL
has a multiple field or composite primary key.
BILL_PAYMENT.PAYMENT_MODE
contains either a value Cash (0)
or a value Other (1)
.
- Data is stored in
PAYMENT_DETAIL
table, if and only if BILL_PAYMENT.PAYMENT_MODE
value is Other
.
- The data is divided into two mutually exclusive or disjoint sets based on the value of
BILL_PAYMENT.PAYMENT_MODE
.
- We connect to the database using ODBC.
- The User DSN is
Bills
.
Table Operations
In a relational system, data from related tables are combined into one table (or view), and then displayed, or used as input to a form or report definition. Thus, the majority of relational database programming involves combining into one table, data from two, three, or more related tables.
Retrieving Mutually Exclusive Records
Once the database is constructed, now is the time to retrieve records from the database. The simplest solution will be:
SELECT BP.BILL_ID, BP.INSTALLMENTNO, BP.INSTALLMENT_AMOUNT,
BP.INSTALLMENT_PAID_DATE, BP.PAYMENT_MODE,
(
SELECT PD.DESCRIPTION
FROM PAYMENT_DETAIL AS PD
WHERE
PD.BILL_ID = BP.BILL_ID
AND
PD.INSTALLMENTNO = BP.INSTALLMENTNO
AND
BP.PAYMENT_MODE = 1
)
FROM BILL_PAYMENT AS BP
WHERE
BP.BILL_ID IN (
SELECT BI.BILL_ID
FROM BILL_INFO AS BI
);
In the preceding subquery solution, it was necessary to examine the second inner query before considering the outer query. That is, the result of the inner query is used to limit the processing of the outer query. In contrast, for other kinds of queries, called correlated subqueries, such as the first inner query, the processing of the inner query depends on data from the other query.
Correlated subqueries may seem difficult to write at first, but once you understand that your query needs to process one table for each row of another table, the use of correlated subqueries becomes clear. Sometimes, depending on how you conceive a query, you can answer the same question by either non-correlated or correlated subqueries or even without using subqueries. Unfortunately, that's what we shall do here. Because, the encircled column in the following snapshot is actually an expression involving column. And Seagate Crystal Reports 8.0 doesn't allow us to use expressions involving columns as fields in a report. Although Seagate Crystal Reports 8.0 supports a special field called the "Formula Field" which allows expressions involving columns, it doesn't allow the use of SQL in Formula Field, which is our requirement here.
Power of SQL
Because relational query languages like SQL are set-oriented languages (that is, commands operate on and generate sets of rows), the equivalent of various set operations may also be available.
Appending Query Results Together
The UNION
command combines the result of two queries into one table as long as the two tables being combined have compatible corresponding columns. That is, the two tables must have the same number of columns, and the corresponding columns must have the same data type. The results from multiple queries may be combined by inserting UNION
between each query.
-
SQL
(
SELECT BI.BILL_ID, BI.ISSUE_DATE, BI.CLIENT_NAME,
BI.TOTAL_AMOUNT, BI.DISCOUNT, BI.BALANCE,
BP.INSTALLMENTNO, BP.INSTALLMENT_AMOUNT,
BP.INSTALLMENT_PAID_DATE, BP.PAYMENT_MODE,
PD.DESCRIPTION
FROM (
BILL_INFO AS BI INNER JOIN BILL_PAYMENT
AS BP ON BI.BILL_ID = BP.BILL_ID
)
INNER JOIN
PAYMENT_DETAIL AS PD ON BP.BILL_ID = PD.BILL_ID
AND
BP.INSTALLMENTNO = PD.INSTALLMENTNO
WHERE
BP.PAYMENT_MODE = 1
AND
PD.BILL_ID = BP.BILL_ID
AND
PD.INSTALLMENTNO = BP.INSTALLMENTNO
ORDER BY
BI.BILL_ID ASC, BI.ISSUE_DATE ASC
)
UNION
(
SELECT BI.BILL_ID, BI.ISSUE_DATE, BI.CLIENT_NAME,
BI.TOTAL_AMOUNT, BI.DISCOUNT, BI.BALANCE,
BP.INSTALLMENTNO, BP.INSTALLMENT_AMOUNT,
BP.INSTALLMENT_PAID_DATE, BP.PAYMENT_MODE,
''
FROM BILL_INFO AS BI, BILL_PAYMENT AS BP
WHERE
BP.PAYMENT_MODE = 0
AND
BI.BILL_ID = BP.BILL_ID
ORDER BY BI.BILL_ID ASC, BI.ISSUE_DATE ASC
}
-
C++
CString Query1, Query2;
Query1 = "SELECT
BI.`BILL_ID`, BI.`ISSUE_DATE`, BI.`CLIENT_NAME`,
BI.`TOTAL_AMOUNT`, BI.`DISCOUNT`, BI.`BALANCE`,
BP.`INSTALLMENTNO`, BP.`INSTALLMENT_AMOUNT`,
BP.`INSTALLMENT_PAID_DATE`, BP.`PAYMENT_MODE`,
PD.`DESCRIPTION`
FROM
(`BILL_INFO` BI INNER JOIN
`BILL_PAYMENT` BP ON BI.`BILL_ID` = BP.`BILL_ID`) INNER JOIN
`PAYMENT_DETAIL` PD ON BP.`BILL_ID` = PD.`BILL_ID`
AND BP.`INSTALLMENTNO` = PD.`INSTALLMENTNO`
WHERE
BP.`PAYMENT_MODE` = 1 AND PD.`BILL_ID` =
BP.`BILL_ID` AND PD.`INSTALLMENTNO` = BP.`INSTALLMENTNO`
ORDER BY
BI.`BILL_ID` ASC, BI.`ISSUE_DATE` ASC";
Query2 = "SELECT
BI.`BILL_ID`, BI.`ISSUE_DATE`, BI.`CLIENT_NAME`,
BI.`TOTAL_AMOUNT`, BI.`DISCOUNT`, BI.`BALANCE`,
BP.`INSTALLMENTNO`, BP.`INSTALLMENT_AMOUNT`,
BP.`INSTALLMENT_PAID_DATE`, BP.`PAYMENT_MODE`,
''
FROM
BILL_INFO BI, BILL_PAYMENT BP
WHERE
BP.`PAYMENT_MODE` = 0 AND BI.`BILL_ID` = BP.`BILL_ID`
ORDER BY BI.`BILL_ID` ASC, BI.`ISSUE_DATE` ASC";
CString Query = Query1+" UNION "+Query2;
m_CrystalReport.SetReportFileName("Report.rpt");
m_CrystalReport.SetWindowTitle("Retrieving Mutually Exclusive
Records with Seagate Crystal Reports");
m_CrystalReport.SetSQLQuery(Query);
m_CrystalReport.PrintReport();
The encircled portion of the report shows, that is just about it. Hope it helps some of you folks out there.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.