«
»

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.

<cfoutput query=”TodaysOrders”>
order: #orderID#, placed by
<cfif val(creatorID)>#customerName#<cfelse>#vendorName#</cfif>
</cfoutput>

Here is another solution – I added creatorIDtype field that indicates if the creatorID is a customer or a vendor:

SELECT orders.*,
name =
CASE e.creatorIDtype
WHEN 'CUSTOMER' THEN customer.name
ELSE vendor.name
END
FROM orders
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.

This entry was posted on 22 May 2011 at 7:40 pm and is filed under SQL. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

6 Comments

  1. Roland Collins says:

    You could also use a COALESCE, which is going to perform better and require no new fields at all!

    SELECT orders.*,
    COALESCE(customer.name, vendor.name) AS name
    FROM orders
    LEFT JOIN customers on orders.creator ID = customers.customerID
    LEFT JOIN vendors on orders.vendorID = vendors.vendor ID

    23 May 2011, 4:55 pm

  2. Ryan says:

    Roland, sorry I had a typo in my last example. I am doing a JOIN here without creating another ID field. So there is no orders.vendorID field, that was a typo. I am joining to either of one of two tables using a single ID field (creatorID).

    Thanks for your comment.

    23 May 2011, 5:46 pm

  3. CephalidOne says:

    or you could do 2 select with cross join and a union

    30 May 2011, 2:47 am

  4. Seth S. says:

    My first thought would be to have a generic person table (e.g., "contact") for the basic and common attributes like name, etc.

    Then have vendor and customer tables that are associated to contact by contactID.

    Then you could use "contactID" as your creatorID.

    Obviously that would be a major re-org, but just thinking theoretically here.

    30 May 2011, 12:56 pm

  5. Ryan says:

    Interesting ideas, thanks for the feedback guys.

    30 May 2011, 7:03 pm

  6. Damien says:

    I'm doing a similar thing – one table with all contacts.
    I'm also doing this for the  products and services tables.
    All relationships between objects are linked through a separate master table 'idlinker'
    In this table, any row can be related to any row in any table.. I'm not sure I explained that very well..
    This means I can have one contact assigned to many locations, orders can be assigned here depending on the accent of the query, etc..

    Im sure there are shortfalls to this method but it works for me..

    13 August 2011, 2:09 am