> orders that do
> not have an associated "cancelled" record, right?
> If so, you could use a self-join. A self-join is where
> you a table to
> itself. To do so, you must specify different table
> aliases (T1 and T2
> in the code below).
> It would look something like this:
> SELECT T1.OrderNbr, T1.OrderThing
> FROM Orders T1 INNER JOIN Order T2 ON T1.OrderNbr =
> T2.OrderNbr
> WHERE T2.Status <> 0
> M!ke
That's going to produce a cartesian result set tho...
Say there are 3 items in the order, and none of them are cancelled,
the database will find
table1.1 table2.1
table1.1 table2.2
table1.1 table2.3
table1.2 table2.1
table1.2 table2.2
table1.2 table2.3
table1.3 table2.1
table1.3 table2.2
table1.3 table2.3
So the number of records returned would be equal to the number of
records in the order squared. It also will still return any
not-cancelled records in an order that has cancelled records, so if
item 3 were cancelled in the above example, he'd get 4 records for the
remaining 2 items in that order...
The cartesian could be eliminated through the use of either the
distinct keyword or a group-by clause, although using a where exists()
query if it's available will solve the original issue and also
eliminate the query.
s. isaac dealey 954.927.5117
new epoch : isn't it time for a change?
add features without fixtures with
the onTap open source framework
http://www.sys-con.com/story/?storyid=44477&DE=1
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
