Crystal Reports & SQL Reporting Services Training Consultants
Account Log in:
Username:
Password:
Freedom is what you do with what's been done to yo
 
Jean-Paul Sartre
Institute of IT Trainers - Freelance Trainer of the Year 2006 & 2009
Liverpool Business Connect Member
  Maximum Impact Solutions Limited - Reporting Solutions, Creating Answers
Reporting Solutions - Creating Answers, Crystal Reports, Dashboarding (Xcelsius) & SQL Reporting Services

The Maximum Impact Solutions Blog Feed ME!!!!

29
September

How Do I Combine Records from Two Separate Tables in Crystal Reports?

I was recently asked by a client "How do I combine records from two separate tables to make a single dataset in Crystal Reports?"

Solution

The most effective way of combining records from separate tables into one dataset is to create a recordset, based on an SQL command, using the SQL UNION function.

There are two UNION flavours:

UNION ALL - Returns all records including duplicates

UNION  - Only returns unique records


Using the Add Command option in the Database Expert dialog box, enter the following SQL code:


    SELECT Fieldname1, Fieldname2, Fieldname3 from Table1


    UNION

 
    SELECT Fieldname1, Fieldname2, Fieldname3 from Table2



Note:

As the UNION ALL function includes duplicates, it tends to execute quicker than the UNION function, as it does not have to remove the duplicates from the dataset.

Some databases will only work with the UNION ALL function


If you have any questions, leave us a comment below, or need any assistance, please do not hesitate to Contact Us
1

Richard Vyse

17
November
I can get this to work but it only returns records which are the same in both databases. I need it to also list the records that are only in one of the 2 databases. Is it to do with the way the tables are joined in database expert? I note that the full outer join option is not available.
2

Julia Emelogu

17
November
Hi Richard, which union statement are you using? UNION or UNION ALL? When you are adding the fields, are you adding the unique fields from each table? The other thing you may want to do is add a where statement to each union dataset, to bring back the exculede records. If you want me to have a look at the SQL statement let me know
3

Lance Premus

10
April
Hello, Can you do the union all when the databases/tables are on different servers? I have a legacy database on a different server and new operations database a new server and We need a report to Union all from both legacy and new database for historical trends.
4

Julia

23
April
Hi Lance, Yes you can, you just need to reference the server as well as the database in the FROM clause

MAKE A COMMENT

Name *

Email Address *

Comment *

To prove you are human what is:

14 + 20 =