[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

How do we combine the results of two SQL statements in Crystal Reports - Basically we are wanting to AND two result sets

We have two separate SQL statements or Commands in a Crystal Report and we want to AND them together.  What is the syntax to do so.  We are looking for a third SQL statement that allows us to AND Query 1 with Query 2 and the results returned are only those records that appear in both query 1 and query2 result sets.
0
rporter45
Asked:
rporter45
1 Solution
 
HoggZillaCommented:
UNION ALL
0
 
rporter45Author Commented:
Could you please expand on this a little more?
0
 
rporter45Author Commented:
We are not looking to output both results.  Both queries return a unique ID.  We want to output unique ID's that are present in query 1 and query 2.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rporter45Author Commented:
How do you call the results from a SQL Command in Crystal to use in another SQL Command?
0
 
rporter45Author Commented:
Query 1 returns a list of unique ID's.  Query 2 returns a list of unique ID's.  Query 1 and 2 are represented in Crystal as separate Commands.

How do I create a third Command or Formula in Crystal that returns a list of unique ID's that are both in Query 1 and Query 2.
0
 
HoggZillaCommented:
Let's try this, if I understand correctly.
 

SELECT DISTINCT x.ID
FROM
(
SELECT DISTINCT a.ID
FROM TableA a
UNION ALL
SELECT DISTINCT b.ID
FROM TableB b
) AS x

Open in new window

0
 
rporter45Author Commented:
This still doesn't work...

It doesn't seem to be referencing the Command (or SQL statements) properly.

SELECT DISTINCT x.ID
FROM
(
SELECT DISTINCT a.ID
FROM Command.0607 a
UNION ALL
SELECT DISTINCT b.ID
FROM Command_1.0708 b
) AS x;
0
 
HoggZillaCommented:
Are you trying to build this like referenced in the following?
http://www.experts-exchange.com/Databases/Crystal_Reports/Q_21292344.html
Whare are the two SQL statements used in the existing two querys? Perhaps we can tweak the syntax. Can you paste here?
0
 
hieloCommented:
>>We have two separate SQL statements
You mean something like:
Q1: Select id FROM table1
Q2: Select id FROM table2

Q3: (SELECT id FROM table1) UNION ALL (SELECT id FROM table2)
0
 
rporter45Author Commented:
I am creating the SQL like this:

When you start to select a datasource you pick the AddCommand option rather than any of the tables.

You have to write the sql as it would be in an Access saved query.

When I add my SQL, it creates a Command for each one I create.  I want to use multiple Command's together in another SQL Command.
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
I've answered this in your other open question:

http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_23858113.html

In a nutshell, you can't do what you're asking.  Likewise, it's a really, really bad idea to link commands in a report do.
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now