"Yasaswi Pulavarti" <ya*****@encfor .com> wrote in message
news:aa******** *************** ***@posting.goo gle.com...
I have a table called order_shipment with primary keys order_id,
order_item_seq_ id etc. I also another table called order_item with
primary keys order_id, order_item_seq_ id etc.
I want all the order_id's which look like '7000%' that are in
order_item but not in order_shipment. Please help me with creating the
sql. I tried UNION, LEFT OUTER JOINS etc. But nothing works, if there
was a MINUS like in Oracle 9i it would solve my problem, but
unfortunately it is not there. Please help.
Seems like you want to find some order items which are not yet shipped?
It is possible that the order_item_seq_ id are different in the order_item
and order_shipment tables, right? If so, how do link one shipped item with
its corresponding order item?
One way I can think of is to give each shipped_order a field order_item_id
that references a row in the order_item table, and make this new column part
of the primary key (or any unique index). Another way is to assume that
each product occurs only once in each order, that is the primary key is
order_id and product_id.
Let's say you go with the second way. First, link all order items to
shipped order items.
select * from
order_item
left outer join order_shipment on order_item.orde r_id =
order_shipment. order_id and order_item.prod uct_id =
order_shipment. product_id
Now assert that no row is found in the order_shipment table, because the
item has not shipped. In other words, any required column from the
order_shipment table must be NULL, meaning that no row was selected.
select * from
order_item
left outer join order_shipment on order_item.orde r_id =
order_shipment. order_id and order_item.prod uct_id =
order_shipment. product_id
where order_shipment. order_id is NULL
Now add you other where clause
select * from
order_item
left outer join order_shipment on order_item.orde r_id =
order_shipment. order_id and order_item.prod uct_id =
order_shipment. product_id
where order_shipment. order_id is NULL and order_id like '7000%'
