Limiting equal join output to first occurrence

[Sumone]

I'm creating a report in v8.5 that accesses three tables.
Table 1 is a bill file linked by customer account number to table 2 which is a customer file. Table 2 is linked in turn by SSN to table 3 which is an employee file. All links are equal joins. I want to only verify that there is an employee record for a given SSN and pick up the employee ID number for output. I wish to do this only for the first occurrence of the SSN, not for every occurrence.

 

[lbass]

You haven't explained the relationships between tables, but let me guess that the bill file has multiple records, the customer file has one record, and the employee file has one or more. One approach would be to select the record for the most recent bill. Create a SQL expression {@maxdate}:

(select max(AKA.`billdate`) from Bill AKA where
AKA.`acctno` = Bill.`acctno`)

Replace "Bill" with your actual table name, and replace "billdate" and "acctno" with your actual field names. Leave "AKA" as is since it is an alias table name.

Then go to report->edit selection formula->RECORD and enter:

{bill.billdate} = {%maxdate}

Since you are using equal joins, only those accounts that have an SSN in the employee table will appear on the report. Is that what you wanted?

-LB