Joining Queries with Multiple Data Links

[rbanister]

I have a report with 2 queries: Q_FIRMS and Q_1. Q_FIRMS is the parent, uses LICENSE_NO as a primary key, and has a G_LIC_NO group. Q_1 returns products for each firm, and has LICENSE_NO as a foreign key. A data link attaches G_LIC_NO.LICENSE_NO to Q_1.LICENSE_NO.

There is a new field, ARCHIVE_LICENSE_NO, in Q_FIRMS. Q_1.LICENSE_NO must use this as a foreign key as well. It allows the query to return products for previous license numbers. This would be fairly straightforward as a query:

SELECT DISTINCT *
FROM firms f, products p
WHERE (
f.license_no = p.license_no
OR f.archive_license_no = p.license_no
)

How would I add a second data link to accomplish this OR functionality? Is a data link the right solution?

Thanks,

Richard

 

[lewisp]

Remove the FK link and create a link that is simply group-to-group. To do this, select the link tool then drag the group header from Q_FIRMS to the header of the Q_1 group.

In your query for Q_1 you can then wite it as

SELECT DISTINCT *
FROM firms f, products p
WHERE  f.license_no = :license_no
OR f.archive_license_no = :license_no