Basic SQL question

From: P B <pbeliveau_at_avior.ca>
Date: Mon, 19 Jan 2004 13:58:33 -0500
Message-ID: <f29o00tqa7f75dcomk539f0sbsq0kk4eck@4ax.com>

First question:

I have two statements that (I think) sould do the same thing, but it appear not. Can someone explain it to me?

It should give me the list of work order for which there is some stock.

1. SELECT WORK_ORDER_ID FROM WORK_ORDER WHERE STATUS='OPEN' AND PART_ID IN (SELECT PART_ID FROM STOCK WHERE QTY GT 0);
2. SELECT T1.WORK_ORDER_ID FROM WORK_ORDER T1,STOCK T2 WHERE STATUS='OPEN' AND T1.PART_ID=T2.PART_ID AND T2.QTY GT 0;

The first statement don't give me all work order.

Second question:

The second question is a bit more complex.

I have the following table:

PART_NBR: list of all part number
CONFIGURATION: list of each possible configuration of each PART_NUMBER

(EX:  	PN A & CONF A, 
	PN A & CONF B, 
	PN A & CONF C,
	PN B & CONF A, 
	PN C & CONF B)

ORDER_ITEM: list of PART_NUMBER ordered by customer ORDER_DELIVERY: list of delivery for each ORDER_ITEM (multiple delivery for each item)

Depending on the delivery of the same item, thus the same part number, the configuration can change (CONF A for the first delivery, CONF B for the second, etc...)

My problem: How do I keep the integrity of this thing? Is it better to have a copy of the part number in each delivery item or create another table ???

thanks Received on Mon Jan 19 2004 - 12:58:33 CST