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
348 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22827227
UNION ALL
0
 

Author Comment

by:rporter45
ID: 22827241
Could you please expand on this a little more?
0
 

Author Comment

by:rporter45
ID: 22827248
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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

Author Comment

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

Author Comment

by:rporter45
ID: 22827405
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22827520
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
ID: 22827967
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
ID: 22828216
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
ID: 22829190
>>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
ID: 22829890
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
ID: 22855775
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

734 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