SELECT mcr.mat_change_req_id
, mcr.line_item_number
, r.remarks
, r.remarks_date
FROM mat_change_req mcr
left outer join mat_change_req_remarks r ON mcr.mat_change_req_id = r.mat_change_req_id
WHERE mcr.contract_id = 'IR-30910'
AND mcr.project_number = '0801082'
</sql>
The problem is that table mat_change_req_remarks (r) has a many-to-one relationship with mat_change_req (mcr) and therefore if r has multiple rows, the above query returns a row for each multiple in r. I would like to only return ONE row from r if there
are multiples where MIN(r.remarks_date). r.remarks_date is a date field. And I am working with Oracle 11g
SELECT mcr.mat_change_req_id, mcr.line_item_number,
r.remarks,
r.remarks_date FROM mat_change_req mcr leftouterjoin( select mat_change_req_id,
remarks,
remarks_date,
row_number()over(partition
by mat_change_req_id orderby remarks_date)as rn from mat_change_req_remarks
) r
ON mcr.mat_change_req_id
= r.mat_change_req_id
and r.rn
=1 WHERE mcr.contract_id
='IR-30910'AND mcr.project_number
='0801082'
Marked as answer by indianapolymath on Nov 09, 2012 04:09 PM
indianapolym...
Member
11 Points
28 Posts
left outer join where left outer join returns one row
Nov 09, 2012 01:43 PM|LINK
I have the following that works fine:
<sql>
SELECT mcr.mat_change_req_id
, mcr.line_item_number
, r.remarks
, r.remarks_date
FROM mat_change_req mcr
left outer join mat_change_req_remarks r ON mcr.mat_change_req_id = r.mat_change_req_id
WHERE mcr.contract_id = 'IR-30910'
AND mcr.project_number = '0801082'
</sql>
The problem is that table mat_change_req_remarks (r) has a many-to-one relationship with mat_change_req (mcr) and therefore if r has multiple rows, the above query returns a row for each multiple in r. I would like to only return ONE row from r if there are multiples where MIN(r.remarks_date). r.remarks_date is a date field. And I am working with Oracle 11g
Thanks in advance!
indianapolym...
Member
11 Points
28 Posts
Re: left outer join where left outer join returns one row
Nov 09, 2012 04:09 PM|LINK
Answer:
SELECT mcr.mat_change_req_id,
mcr.line_item_number,
r.remarks,
r.remarks_date
FROM mat_change_req mcr
left outer join (
select mat_change_req_id,
remarks,
remarks_date,
row_number() over (partition by mat_change_req_id order by remarks_date) as rn
from mat_change_req_remarks
) r ON mcr.mat_change_req_id = r.mat_change_req_id and r.rn = 1
WHERE mcr.contract_id = 'IR-30910' AND mcr.project_number = '0801082'