mysql - Structuring tables and joining records based on lowest occurring value -

i need set query runs reports sales per sales team. there 3 tables involved in following simplified model.

sql fiddle here: http://sqlfiddle.com/#!9/b09b3a/

**users_table** id     name 1      john doe   **sales_team** user_id       sales_team             date_joined_team 1             sales team 1           jan 1st 1             sales team 2           jan 5th 1             sales team 3           jan 10th 1             sales team 2           jan 15th  **transactions** user_id        transaction_date  amount 1              jan 2nd           20.00 1              jan 3rd           10.00 1              jan 7th           15.00 1              jan 11th          13.00 1              jan 16th          5.00 

i need join on proper sales team on transactions table, using user id , lowest possible date_joined team greater transaction_date each transaction

**desired results** sales_team                date            amount sales team 1              jan 2nd         20.00 sales team 1              jan 3rd         10.00 sales team 2              jan 7th         15.00 sales team 3              jan 11th        13.00 sales team 2              jan 16          5.00 

i'm not asking whole query, on how join on data based on highest date less than specified date.

thanks!

something this:

select date, amount, (     select st.sales_team     sales_team st     st.user_id = t.user_id       , st.date_joined_team < t.date     order st.date_joined_team desc     limit 1) sales_team transactions t 

if want "lowest possible date_joined team greater transaction_date", change

      , st.date_joined_team < t.date     order st.date_joined_team desc 

to

      , st.date_joined_team > t.date     order st.date_joined_team asc