Re: Basic SQL question

From: Ed prochak <ed.prochak_at_magicinterface.com>
Date: 22 Jan 2004 10:43:33 -0800
Message-ID: <4b5394b2.0401221043.6bd02eef@posting.google.com>

P B <pbeliveau_at_avior.ca> wrote in message news:<f29o00tqa7f75dcomk539f0sbsq0kk4eck_at_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.

WHAT DB accepts GT as the operator?

If you mean >, then I would expect they produce the same. Let's see. table WORK_ORDER
has at least columns: WORK_ORDER_ID STATUS PART_ID ...others? table STOCK
has at least columns: PART_ID QTY ...others?

Sorry, I'm stumped. Are you sure those are the queires you tested?

Your second question just is a somewhat poor design. Fix the design. (remember: the key and only the key)

hth.
Ed Received on Thu Jan 22 2004 - 12:43:33 CST