The Contacts table must also be partitioned among the sales representatives, but contains no reference to the sales representative rep_key value. How can the Message Agent match a subscription value against rows of this table, when rep_key is not present in the table?

To solve this problem, you can use a subquery in the Contacts article that evaluates to the rep_key column of the Customers table. The publication then looks like this:

CREATE PUBLICATION SalesRepData (
   TABLE SalesReps
   TABLE Customers
      SUBSCRIBE BY rep_key
   TABLE Contacts
      SUBSCRIBE BY (SELECT rep_key
         FROM Customers
         WHERE Contacts.cust_key = Customers.cust_key )
);

The WHERE clause in the subscription expression ensures that the subquery returns only a single value, as only one row in the Customers table has the cust_key value in the current row of the Contacts table.