SQL Join by Date Range

I'm having a bit of a frustrating time with a SQL query and could really use the help. Basically, I have a table of customer interactions (sales visits), which has a customer_id, a sales_location and an visit_date. I have a separate table that has orders resulting from that visit. Some visits resulted in zero orders while others resulted in multiple orders. The order table has the customer_id and order_date with each row representing a distinct order.

I need to create a merged table that has a row for every order or visit linked to the sales_location where the order originated from. Basically, the tables have been designed poorly and we need to understand which sales locations are producing a lot of orders per customer visit.

What I need to do, and the piece I'm struggling with, is to use SQL to implicitly figure out, based on the order_date, which interaction it was associated with for that customer_id. For example, let's say we interacted with customer #463 in Reno and then in Dallas, there would be two interactions on two dates. We may have had 3 orders as a result of the Reno trip 开发者_如何学Goand one order as a result of the Dallas trip (or none). I need to use the dates of the orders to figure out which sales location the order came from. What is the best way to do this?

---

Assuming that the last visit before the order is associated with an order.

And that no customer visited two (or more) different locations the same day (actually this would show in the results as having both visits associated with next sales).

SELECT s.customer_id
     , s.sales_location
     , s.visit_date
     , g.customer_id
     , g.order_date
FROM sales_location s
  FULL JOIN                       <--- replace with LEFT JOIN
    ( SELECT o.customer_id
           , MAX(s.visit_date) AS last_visit_date
           , o.order_date
      FROM order o 
        LEFT JOIN sales_location s
          ON  s.customer_id = o.customer_id
          AND s.visit_date <= o.order_date
      GROUP BY o.customer_id
             , o.order_date
    ) AS g
    ON  g.customer_id = s.customer_id
    AND g.last_visit_date = s.visit_date

You can replace the FULL JOIN with LEFT JOIN. Only effect will be that orders that can't be asscoiated with a visit (orders that were done without any previous visits of the customer to any sales_locations) won't be shown.