Our end users have many ways of shipping product to our customers. They can use the customer address in one table (
[customer]), a specific delivery address in another (
[dropship]), or yet another delivery address in another table (for customers with multiple locations) (
[delivery]). These all have roughly the same structure, with the same column names for the same data points (e.g.
[address] is the address in all tables).
Currently, our logic for finding the delivery address of an order is as follows:
SELECT Address = COALESCE(r.address, d.address, c.address) FROM [order] o JOIN customer c ON o.customerid = c.customerid LEFT JOIN delivery d ON o.customerid = d.customerid AND o.delivaddressid = d.delivaddress LEFT JOIN dropship r ON o.orderid = r.orderid;
However, this is a new paradigm to me and I'm trying to wrap my brain around it. Do I just replace the
LEFT JOINs with
WHERE EXISTS (select 1 from delivery d where o.customerid = d.customerid and o.delivaddressid = d.delivaddress)
This won't allow me to use the data from
[delivery] unless I toss it in the
FROM clause and create a cartesian product, right?