How to join a table, more than once

[former_member731159]

Hello ,

I have more than ten (10) tables that I am linking (usually through LEFT OUTER JOINS). Is there a way to use one of the tables more than once .

Example:

Select * from A LEFT OUTER JOIN B ON...

LEFT OUTER JOIN C ON...

LEFT OUTER JOIN D ON...

LEFT OUTER JOIN E ON...

LEFT OUTER JOIN F ON...

LEFT OUTER JOIN (embedded SQl that uses table D ) ON...

When trying to do this with Database expert, from the link section, I do not see a way to use a table more than once. I would need to create a view in the database and use it.

Thanks,

Paul

[DellSC · Accepted solution]

Yes, this is possible. In the Database Expert you need to add the same table to the report more than once. The second time you add it, Crystal will through a warning that the table is already in the report so it will requires an "alias". It will then add the table a second time with "_1" at the end of the table name. You can add the table as many times as you need it and Crystal will alias with "_2", "_3", etc.

What I usually do at this point is select the alias in the list of tables that have been added and either press F2 to rename or right-click and select "Rename". You can then rename the alias to something more meaningful. For example, if I have a "Person" table that includes a manager id that would link back to the Person table, I would add the table a second time, rename the alias as "Manager", and link from the manager ID in Person to the person ID in Manager to get the manager name. You can leave the alias as Crystal sets it, but I think it's easier to track what the data is if you rename the alias according to what it's being used for.

-Dell

[former_member731159 · Accepted solution]

Thanks Dell. This is a game changer.

I have one additional question. I'm doing this within a sub report. The table that I want to use more than once is a linking table to the main report. Which means the table has a filter on it and will only show data that corresponds to the filter or data that meets the parameter that is being passed to it. Will the alias table show all rows of data or will it only show data for the parameter that is being passed in.

[DellSC · Accepted solution]

That depends on how you link the tables together in the subreport along with how you apply the filter from the links from the main report. If the links go to the first copy of the table and not the second, then the first table will be filtered by the links and the second won't - it's data will appear based on the way the second table is linked to other tables in the report.

-Dell

[former_member731159 · Accepted solution]

Thank you.