Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: How do I show multiple items from 2 tables? Post Reply Post New Topic
Author Message
proone
Newbie
Newbie
Avatar

Joined: 06 May 2011
Location: United States
Online Status: Offline
Posts: 7
Quote proone Replybullet Topic: How do I show multiple items from 2 tables?
     Posted: 29 Feb 2012 at 6:56pm
           table_1a
table_1 <
           table_1b

Table 1 is connected to table 1a & 1b by a unique identifier.

I want to show all data where UID = whatever UID I choose from table 1.

1a might have 1 or 10 rows of data which display just fine.

After the 10 rows of data from 1a I want to show the data from 1b which may have 1 or 5 rows of data.

If I put 1b in the footer, I only get 1. If I put 1a in details a and 1b in details b, it doesn't report 1b accurately and it mixes them together.

How do I accomplish this?   Thank you in advance for any suggestions!

P.S. I tried a subreport which listed the 1b data 10 times and if I chose suppress duplicate data, then I have a huge blank box on my report.
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet Posted: 29 Feb 2012 at 10:30pm

You could try this in a SQL statement using a union to make 1a and 1b 1 table before performing the join

eg;
 
SELECT
table_1.uniqueID,
table_1.field1,
table_1.field2,
table_1ab.mergedcolumn
from table_1
JOIN (
SELECT
table_1a.uniqueID as 'UniqueID',
table_1a.desiredfield as 'mergedcolumn'
from table_1a
UNION ALL
SELECT
table_1b.uniqueID as 'UniqueID',
table_1b.desiredfield as 'mergedcolumn'
from table_1b) table_1ab
ON table_1ab.uniqueID = table_1.uniqueID
 
Hope that helps.
 
Regards,
Ryan.


Edited by rkrowland - 29 Feb 2012 at 10:33pm
IP IP Logged
proone
Newbie
Newbie
Avatar

Joined: 06 May 2011
Location: United States
Online Status: Offline
Posts: 7
Quote proone Replybullet Posted: 01 Mar 2012 at 3:02am
Thank you for your response! I'm not sure where to put the SQL query. Do I create a command?
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet Posted: 01 Mar 2012 at 4:18am
Yeah, create a command on your database.
 
You'll need to adjust the query to include the correct tables & fields you need.
 
Regards,
Ryan.


Edited by rkrowland - 01 Mar 2012 at 4:20am
IP IP Logged
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum