SQL - Cannot get JOIN do what I want -

SQL - Cannot get JOIN do what I want -

i have 3 tables: tab1 having client id, start date , end date tab2 having type 1 transaction data: client id, transaction date, transaction amount tab3 having type 2 transaction data: client id, transaction date, transaction amount

i want have table next columns: col1: player_id col2: sum of transaction amount tab2 (type 1 transaction) occurred between associated start date , end date tab1 col3: sum of transaction amount tab3 (type 2 transaction) occurred between associated start date , end date tab1

i got work if bring together tab1 , tab2 joining 2 tables on player id , using:

sum(case when (transaction date >= start date , transaction date <= end date) transaction amount else 0 end)

but when bring together tab3 on player id, previous sum gives me different , much higher (and wrong) values. i'm sure i'm making mistakes on cartesian product i'm creating,

instead of bring together tab3 tried subquery on tab3, netezza sql did not allowed me that...

i have no thought how prepare it...

**update after comments***

this query i'm trying run:

select lm.player_id, /*sales before limit starts */ sum(case when (dt.cal_day_dt<=lm.hit_time , dt.cal_day_dt>lm.hit_time-14) .sales_amount else 0 end) sales_before14, /*sales before limit starts - command grouping */ (select sum(amount_won) ia_player_winners transaction_time<=lm.hit_time , transaction_time>lm.hit_time-14) sales_before14_control ( select player_id, min(hit_time) hit_time, re_enable_date, limits ia_player_spending_limits limit_type='w' grouping player_id, re_enable_date, limits ) lm bring together ia_player_sales_hourly s on lm.player_id = s.player_id bring together ia_dates dt on s.date_key = dt.date_key bring together ia_products pr on s.product_key = pr.product_key lm.hit_time >='2014-03-25' , lm.re_enable_date<='2014-05-24' grouping lm.player_id, lm.re_enable_date, lm.limits order lm.player_id;

i error: "relation 'lm' not exist"

when tried eliminate reference lm, got new error: "sub-select uses un-grouped attribute "rss".#hit_time#0xa69fca4 outer query"

you need 2 separate queries similar structure, 1 tab2 , 1 tab3 (or 2 subqueries, or 2 or 3 views, depending on sql dialect allows), because tab2 , tab3 unrelated; cannot bring together them in sensible way.

what happens in tests is, probably, bring together of 3 tables repeats rows in tab2 each row in tab3 having same customer_id , vice versa, summing multiple copies of each transaction.

sql join