help with a query

Database

help with a query

All,

Looking for some help with a query.

I am combining two different tables.

One table is an order table that lists Order status, and other fields.

I am doing an inner join to the Order_product table

what this table does is list all of the products associated with an order. One row per product, so a single order number can have multiple rows.

Order Table example

Order_Num Order_Status
1000 open
1001 error
1002 open

Order_Product example

Order_Num Product_ID
1000 500
1000 510
1000 540
1001 500
1001 540
1002 500
1002 540

What I am trying to do is write a query that produces all orders that do not have the 510 product_id.

Essentially my quey should out put

Order_num
1001
1002

3 REPLIES
Teradata Employee

Re: help with a query

SELECT O.Order_Num FROM Order_Table O
WHERE NOT EXISTS
(SELECT 1 FROM Order_Product OP
WHERE O.Order_Num = OP.Order_Num AND OP.Product_ID=510);
Enthusiast

Re: help with a query

Hi logic997,

Try the following query:

sel order_num from orders a inner join
(sel order_num,product_id from order_product where order_num not in (sel order_num from order_product where product_id =510))dt(onum,prodid)
on a.order_num=dt.onum group by 1

cheers:-)

Re: help with a query

Thanks Guys, that where not exists is exactly what i was looking for.

Smilesever I couldn't get your query to work, but i think i get the concept you are hinting at.