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
345 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
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

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 …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

770 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