Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.
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.