AnsweredAssumed Answered

Searching through join tables

Question asked by PeterThorton on May 14, 2012
Latest reply on May 16, 2012 by PeterThorton


Searching through join tables



I have a realtively simple problem. I have a table of customers. Each customer can have multiple addresses assigned to them. When the user is entering an order, they pick one or two addresses (billing and shipping address if they're not the same) and put them on the order form. So far it's easy.

But some of our customers are actually dealers, and they have a set of customers to whom they sell our products. Sometimes the order has to be made to the dealer, with the dealer's data and billing address, but with their customer's address as the delivery address.

I've prepared the data structure that supports this, it's in the picture. BusinessPartner is the actual table that stores my customers (and vendors, therefore the vague name) CustomerDealer is, as you can see, a join table. Turns out more than one dealer can sell to the same customer (and of course one dealer has multiple customers) so it's a many-to-many relationship. RelatedCustomer is just another table occurence of BusinessPartner (since the dealers' customers are also our customers)

What I don't know is how to perform a search that would return the required records. I start with an ID (the ID of the dealer whose customers' addresses I want to list). I want to find all the addresses of all the customers related to this dealer (an entry in the CustomerDealer exists for the customer-dealer pair). How can I do it?