ELIMINATING UNWANTED RECORDS IN UNION?

[osjohnm]

Hi all

I have been fighting with the foolowing query for a day and a half now. This query is for a report. It revolves around 4 major tables, order_group, order_header, purchase_header, purchase_order.

order_group is made up of multiple order_header records.
eg. order_group_no = 2020805-0001. there is a order_grouP-no in the order Header table to join the two. orders are slpit up into modules. eg:
order_no order_group_no
IT2020805-001 2020805-001
NW2020805-001 2020805-001

purchase header contains a order_no column to join back:
PO_No Order_No
001 IT2020805-001

purchase_order contains all the line items for the purchase_order and joins on po_no

the problem is that not every order_header has a purchase order attachted to it. so u might find a order_group that has 5 headers but only one header has a purchase order.

I tried a union where the first query does a straight join and the second part i used a not in statement to get the ones that don't has any purchase orders.

sample data:
order group cost centre auth amount po amount
2020502-0035 00975 5453.0000 .0000
2020503-0011 00975 28569.6400 28635.0400
2020508-0008 00975 792.7674 .0000

this seems to be happening as well
order group cost centre auth amount po amount
2020704-0004 00840 34866.1360 .0000
2020704-0004 00840 34866.1360 34795.0000


here is my query

-- PO
select g.order_group_no, u.cost_centre, g.authorised_value[Auth Amount], sum(po.value)[PO Amount]
from order_group g, order_header h, purchase_header ph, purchase_order po, user_info u
where g.order_group_no = h.order_group_no
and h.order_no = ph.order_no
and ph.po_no = po.po_no
and ph.status_code <> 'cancelled'
and h.auth_date > '2002-07-01'
and h.auth_date < '2002-08-01'
and g.requestor = u.user_id
group by g.order_group_no, u.cost_centre, g.authorised_value
union
--no purchase orders
select g.order_group_no, u.cost_centre, g.authorised_value[Auth Amount], 0
from order_group g, order_header h, user_info u
where g.order_group_no = h.order_group_no
and h.order_no not in (select order_no from purchase_header where order_no is not null)
and h.auth_date > '2002-07-01'
and h.auth_date < '2002-08-01'
and g.requestor = u.user_id
group by g.order_group_no, u.cost_centre, g.authorised_value
order by g.order_group_no

is there anyway to get only one order_group_no plus its corresponding data to appear?

I would appreciate any help

thanks
john

 

[EdVassie]

You are getting two rows returned because you are asking for them in your query.

The process of removing duplicates in the union considers all fields that are being returned in the result set. Your 'group by' within each side of the union is redundant, because the elimination of duplicates will repeat everything that is in your 'group by', plus filtering on the 'po amount' field.

I suggest the best way to tackle this is to use a left outer join.

If you have not used these, then start with something simple, then build up to your full query.

This posting is a personal opinion only, and may not reflect reality.