How to Query Data from Multiple Tables in a Crystal Report Using Command Text?

Querying Data from Multiple Tables in a Crystal Report

When creating a report in Crystal Reports using ODBC, you may need to retrieve data from multiple tables. To accomplish this, you can utilize the "Command Text" feature in the Crystal Report wizard.

Locating the Command Text Field

After connecting to your ODBC data source and selecting the database expert to add tables, navigate to the Crystal Report wizard. Within the wizard, locate the "Set Database Options" dialog box.

Inserting the Query

In the "Set Database Options" dialog box, find the "Command Text" field. This field allows you to enter your SQL query directly into the report.

Example Query

Consider the following query, which retrieves data from three tables:

SELECT ts.`SCHEDIDNO`,
       ts.`DAYNAME`,
       DATE_FORMAT(ts.`TIMESTART`, '%h:%i %p') as TIMESTART,
       DATE_FORMAT(ts.`TIMEEND`, '%h:%i %p') as TIMEEND,
       ts.`GRADELEVEL`,
       ts.`SECTIONNAME`,
       ts.`SUBJECTNAME`,
       ts.`FACFULLNAME`,
       ts.`ROOMNAME`,
       tf.`FACFULLNAME` as PERSONNEL,
       tf.`DEPARTMENT`,
       tse.`Adviser`
FROM `tblschedule` ts,
     `tblfaculty` tf,
     `tblsection` tse
WHERE ts.`GRADELEVEL` = " & lblgrade.Text & "
AND ts.`SECTIONNAME` = '" & lblsect.Text & "'
AND ts.`DEPARTMENTNAME` = tf.`DEPARTMENT`
AND tf.`ADMINISTRATOR` = 1
AND tse.`SECTIONNAME` = '" & lblsect.Text & "'

Integrating the Query into the Report

Paste the query into the "Command Text" field. By doing so, Crystal Reports will use the specified query to retrieve the data needed for your report.

Generating and Viewing the Report

Once you finalize the query, complete the remaining steps in the Crystal Report wizard. You can then preview and generate the report, which will display the data retrieved from the multiple tables specified in the query.

The above is the detailed content of How to Query Data from Multiple Tables in a Crystal Report Using Command Text?. For more information, please follow other related articles on the PHP Chinese website!