Solved

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

Posted on 2008-10-28
11
343 Views
Last Modified: 2008-11-01
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
Comment
Question by:rporter45
11 Comments
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
UNION ALL
0
 

Author Comment

by:rporter45
Comment Utility
Could you please expand on this a little more?
0
 

Author Comment

by:rporter45
Comment Utility
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
 

Author Comment

by:rporter45
Comment Utility
How do you call the results from a SQL Command in Crystal to use in another SQL Command?
0
 

Author Comment

by:rporter45
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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
 

Author Comment

by:rporter45
Comment Utility
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
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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
 
LVL 82

Expert Comment

by:hielo
Comment Utility
>>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
 

Author Comment

by:rporter45
Comment Utility
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
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 500 total points
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now