I came across an interesting problem the other day where I needed to join to either one of two different tables. Here is an example scenario:
Say you have an ecommerce site that your customers log into. Customers exist in a table named “customers”. When they place an order, an order record is created in the orders table. There is a CreatorID field that indicates which customer placed the order. A common practice when creating order reports is to join the order to the customer in order to be able to display the customer contact information when displaying the order.
SELECT orders.*, customers.name FROM orders
JOIN customers on orders.creator ID = customers.customerID
Later you are given the task of allowing vendors to place orders. Vendors also log into your site but their accounts are stored in a separate vendors table. So now you are faced with the question of how to link orders to both the customers and vendors table. You could add a vendorID field to the orders table, then change your join to connect to both tables:
SELECT orders.*, customers.name AS customerName, vendors.name AS vendorName FROM orders
LEFT JOIN customers on orders.creator ID = customers.customerID
LEFT JOIN vendors on orders.vendorID = vendors.vendor ID
The LEFT JOIN here is necessary so we still get an order record even when there is not a match in the customers or vendors table. The problem I had with this approach is it requires changing a lot of other code on the system. Because the “name” column is ambiguous between the two tables, we had to alias the “name” column to be different for each of the two tables. So now all your view code would have to be changed to look at one of those two columns.
order: #orderID#, placed by
Here is another solution – I added creatorIDtype field that indicates if the creatorID is a customer or a vendor:
WHEN 'CUSTOMER' THEN customer.name
LEFT JOIN customers on orders.creatorID = customers.customerID
LEFT JOIN vendors on orders.creatorID = vendors.vendorID
Now the view code does not need to be changed, the “name” column will continue to hold the name of the person who placed the order, regardless if it was a customer or a vendor.