YelizavetaYR YelizavetaYR - 5 months ago 8
MySQL Question

Query is not joining correctly, how do I fix it?

I have a large query that is structured like this:

There are a bunch of tables the main table

LT_CHC_TOURS_RSV_CS
has a
t_dt
column.

There is
LTR_CHC_TOURS_RSV_PRICE
that has a
season
value.

There is also
TR_SEASON
has a
season
value of its own (corresponding to the
LTR_CHC_TOURS_RSV_PRICE
) it also has a
start_dt
and
end_dt
fields

What I want to say in my query is that the join has to be
p.season
field has to correspond to the
a.t_dt
date falls in between
tr_season
start_dt
and
end_dt
and i don't know how to do this.

I'm not sure if I'm explaining this correctly.

Tables

tr_season

season desc start_dt end_dt
1 s1 1/1/2013 12/31/2013
2 s2 1/1/2014 12/31/2014
3 s3 1/1/2015 12/31/2015


LTR_CHC_TOURS_RSV_PRICE

season price1 price2 price3
1 4 5 7
1 2 6 8
1 7 5 9


LT_CHC_TOURS_RSV_CS

t_dt count
2/5/2013 6
2/7/2014 2
8/2/2015 3


Season's get matched up and counts will be multiplied by pricing.

My query (unfinished)

select * -- a bunch of stuff
FROM LT_CHC_TOURS_RSV_CS a
LEFT OUTER JOIN lv_customer_info1 b on a.grp_attending = b.customer_no
LEFT OUTER JOIN lv_chc_order_detail_info o on a.order_no = o.order_no
LEFT OUTER JOIN LTR_CHC_TOURS_RSV_PRICE p on /*this is where i'm stuck*/

Answer

What about something like this?

select * -- a bunch of stuff 
FROM LT_CHC_TOURS_RSV_CS a
LEFT OUTER JOIN lv_customer_info1 b         on a.grp_attending = b.customer_no
LEFT OUTER JOIN lv_chc_order_detail_info o  on a.order_no = o.order_no 
JOIN TR_SEASON s     on a.t_dt between s.start_dt and s.end_dt
JOIN LTR_CHC_TOURS_RSV_PRICE p   on s.id = p.season 

This way you can look at your t_dt field and it should be between the start_dt and end_dt and then use the id in that field for your join.

Comments