August 28, 2008 at 8:56 am
The objective is to create a view that displays all receivers and associated purchase orders.
There are three tables for recievers and three tables for purchase orders.
ex: header/line/line delivery
so we have r,rl,rld and po,pol,pld
The relationships are: 1 to many to many
The problem:
The result set incudes duplicate rows when a PLD record is found.
It may do the same for RLD but the results don't make this clear.
Here is the join:
from
receiver_line rl
join receiver r
on (r.id=rl.receiver_id)
left outer join purchase_order po
on (po.id=rl.purc_order_id)
left outer join vendor v
on (po.vendor_id=v.id)
left outer join receiver_line_del rld
on (rl.receiver_id = rld.receiver_id and rld.receiver_line_no=rl.line_no)
left outer join purc_order_line pol
on (rl.purc_order_id = pol.purc_order_id and rl.purc_order_line_no = pol.line_no)
left outer join purc_line_del pld
on (pol.purc_order_id = pld.purc_order_id and pld.purc_order_line_no = pol.line_no)
How can I cause the duplicate rows not to be included in the result set?
August 28, 2008 at 10:43 am
issue resolved
thank you
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply