All Forums Database
logic1977 4 posts Joined 12/08
01 Mar 2009
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 tablewhat 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 exampleOrder_Num Order_Status1000 open1001 error1002 openOrder_Product exampleOrder_Num Product_ID1000 5001000 5101000 5401001 5001001 5401002 5001002 540What 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 putOrder_num10011002

Fred 1096 posts Joined 08/04
01 Mar 2009

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

smilever 45 posts Joined 10/07
02 Mar 2009

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 1cheers:-)

logic1977 4 posts Joined 12/08
02 Mar 2009

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.

You must sign in to leave a comment.