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.