CODEWITHSUNDEEP
mysqljoinleft-join
YelizavetaYR
YelizavetaYR

Reputation: 1701

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*/

Upvotes: 1

Views: 56

Answers (2)

Elizabeth
Elizabeth

Reputation: 765

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.

Upvotes: 1

Jon
Jon

Reputation: 636

Without a database to work with for testing, I think you're looking for something similar to the following. I'm not exactly a SQL expert, but I think something like this would work.

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 p.season = (
    SELECT season 
    FROM tr_season s 
    INNER JOIN LTR_CHC_TOURS_RSV_PRICE pr 
    ON pr.season = s.season
    WHERE (s.start_dt < pr.t_dt AND s.end_dt > pr.t_dt)
)

Upvotes: 0

Related Questions