oracle - SQL join allowing only one match from each table -

i have 2 tables. 1 table has coupons allocated each customer , other table has redemption information each customer. need left coupons redeemed each campaign , if upc overlaps 2 campaigns counted both (but not counted twice within 1 campaign). here's idea of redemtion table

| customer_id | upc  | redeem_date_id | |-------------|------|----------------| |     1234    | 3456 |       42       | |     1234    | 3456 |       43       | |     1234    | 3456 |       44       | |     1234    | 3456 |       49       | 

and table coupons allocated looks

 | customer_id | campaign_id | upc  | print_date_id | expire_date_id |  |-------------|-------------|------|---------------|----------------|  |    1234     |      1      | 3456 |      35       |       45       |  |    1234     |      1      | 3456 |      40       |       50       |  |    1234     |      2      | 3456 |      41       |       51       | 

in example customer has more redemptions allocated coupons (because could've clipped coupon somewhere etc..) have more allocated coupons redemptions.

obviously if

where a.customer_id = b.customer_id , a.upc=b.upc ,  redeem_date_id between print_date_id , expire_date_id 

i'm going way more records need. don't want same redemption counted more once each campaign, , don't want more coupons had. exception is, same redemption can counted different campaigns not within 1 campaign. (so if redemption table had first observation want output table have 2 redemptions - 1 either coupon in campaign 1 -- don't care -- , 1 campaign 2.)

it's allocation problem - within campaign want in redemption table match - join - match next observation (without reusing first matching redemption). 1 of many possible ways output table is:

| customer_id |campaign_id|upc |print_date_id|expire_date_id|redeem_date_id| |-------------|-----------|----|-------------|--------------|--------------| |    1234     |      1    |3456|     35      |        45    |      42      | |    1234     |      1    |3456|     40      |        50    |      43      | |    1234     |      2    |3456|     41      |        51    |      42      | 

any appreciated

you pick minimal date using subquery. like:

select * inner join b on a.customer_id = b.customer_id    , a.upc = b.upc    , redeem_date_id between print_date_id , expire_date_id b.print_date_id = (    select min(print_date_id)    b b2    b2.customer_id = a.customer_id    , b2.upc = a.upc    , a.redeem_date_id between b2.print_date_id , b2.expire_date_id ) 

though i've not tested (yet).

but else bothering me. check coupons

 | customer_id | campaign_id | upc  | print_date_id | expire_date_id |  |-------------|-------------|------|---------------|----------------|  |    1234     |      1      | 3456 |      35       |       45       | 

and redemption data

| customer_id | upc  | redeem_date_id | |-------------|------|----------------| |     1234    | 3456 |       42       | |     1234    | 3456 |       43       | 

they don't hold information discriminate whether coupon valid @ dates 35-45 redeemed @ date 42 or 43.

in other words, information appears missing: coupon redeemed @ time? there no unique id each coupon? can not record that?

i think while it's possible find technical solution problem today, you're not recording information complete enough type of application.