Crystal Report Database Query

[Former Member]

Hi I would like to ask regarding queries from the database. I have 2 main tables that gets their images from 1 master table.

First is TASK table and Second is ASSET table. Both tables are relational.

Each Task has Asset connected by Task_Asset_ID.

But both tasks and each assets may have also images from the Image table thru Assignment table. But I am wondering how could I query the data to show tasks with images and assets with images in one row.

For example

Task 1 may have Green.jpg

Asset 222 may have Red.jpg

Knowing that Task and Asset are relational, is it possible to show them in one row in crystal report?

.

Task

TaskID	TaskName	Task_Asset_ID
1	Task1	111
2	Task2	222
3	Task3	111

Asset

AssetID	AssetName
111	Asset1
222	Asset2

Images

imgID	ImgName
1	Red.jpg
2	Yellow.jpg
3	Blue.jpg
4	Green.jpg

Assignment

AssID	TaskOrAssetID	ImgID	Type
1	1	4	X
2	222	1	Z
3	3	3	Z
4	111	2	X

[DellSC · Accepted solution]

Ok, this is going to get a bit complicated, but this is possible.  The problem is that the Assignment table records can have either TaskID or AssetID, so you're going to have to use some tables twice with aliases.

Here's what I'd do...

In the Database Expert:

1.  Add all of the tables to your report.

2.  Add a second copy of Assignment and Images - when you do this, Crystal will warn you that the table is already in the report and ask if you want to "alias" it.  At that point it will add the table with "_1" at the end of the table name.  So, you'll have Assignment_1 and Images_1.

3.  On the linking tab, create the following links:

     Task.Task_Asset_ID to Asset.AssetID

     Task.TaskID to Assignment.TaskOrAssetID - make this a left outer join (see below)

     Assignment.ImgID to Images.ImgID - make this a left outer join

     Asset.AssetID to Assignment_1.TaskOrAssetID - make this a left outer join

     Assignment-1.ImgID to Images_1.ImgID - make this a left outer join.

To make a left outer join, right-click on the join and select "Join Options".  Select "Left Outer" and save the join.  Left outer joins will provide data even when there is no matching value in the table that you're linking to - the values from that table will be null.

In your report:

1.  Put the following column headers on the report:

     Task Name

     Task Image

     Asset Name

     Asset Image

2. Put the following fields in the details of the report:

    {Task.TaskName}

    {Images.ImgName}

    {Asset.AssetName}

    {Images_1.ImgName}

3. Sort the data by Task Name and Asset Name.

-Dell

[Former Member · Accepted solution]

Thank You!