SELECT * FROM table WHERE id NOT IN? Help with statement.

"tradmusic.com" <sales@NOSHPAMtradmusic.com> wrote in message
news:ee3n5m$gqe$1@forums.macromedia.com...
> I need it to be:
> (orderID IN (SELECT qryPaidOrders.orderID FROM qryPaidOrders) ORDER BY
> orderdate ASC) AND ((customerid = MMColParam1) OR (vanopID = MMColParam2))

The way you have it written above, you've mixed WHERE and ORDER BY, but
you've finally got your and/or mess straightened out. :)

WHERE orderID IN (SELECT qryPaidOrders.orderID FROM qryPaidOrders) AND
(customerid = MMColParam1 OR vanopID = MMColParam2)
ORDER BY orderdate ASC

You originally had it written with no parentheses around your (X or Y),
which meant the conditions were not being interpreted the way you thought
they were. X OR Y AND Z == X OR (Y AND Z) due to order of operations, but
you wanted (X OR Y) AND Z.

That being said, this is the absolute slowest way you could write that
query. It will bog down quickly once you start getting any significant
number of orders in the system. For optimal performance, do an outer join
and keep only rows without matches.

SELECT <list your fields - DO NOT USE * IN PRODUCTION CODE EVER!!!!!>
FROM dbo.Orders O
LEFT OUTER JOIN dbo.PaidOrders P ON O.OrderID=P.OrderID
WHERE O.CustomerID=MMColParam OR O.VanopID=MMColParam AND P.OrderID IS NULL
ORDER BY O.OrderDate

That will be MUCH faster.

Hi Nancy,

Before I begin that, can I ask if there is an issue with the OR?

I need it to be:
(orderID IN (SELECT qryPaidOrders.orderID FROM qryPaidOrders) ORDER BY
orderdate ASC) AND ((customerid = MMColParam1) OR (vanopID = MMColParam2))

MMColParam1 is exactly the same as MMColParam but, for some reason
unbeknowns to me, DW8.0.2 won't allow me to use it twice. That's neither
here nor there though, as creating a 2nd variable (MMColParam2) solves that
particular issue.

Do I need additional ( ) anywhere considering it is an OR though?

Thanks.
nath.

"Nancy *Adobe Community Expert*" <nancy@webwish.com> wrote in message
news:ee3m37$fgq$1@forums.macromedia.com...
>I would think so .. now that you have the core statement working .. try
>adding the other portions. Put the main statement in ( ) and add the
>others. Like this:
>
> SELECT *
> FROM dbo.qryOrders
> WHERE (orderID IN (SELECT qryPaidOrders.orderID FROM qryPaidOrders)
> ORDER BY orderdate ASC) AND (customerid = MMColParam1) AND (vanopID =
> MMColParam2)
>
> See what that gives you and fiddle with those till you get what you want.
>
>
> --
> Nancy Gill
> Adobe Community Expert
> BLOG: http://www.dmxwishes.com/blog.asp
> Author: Dreamweaver 8 e-book for the DMX Zone
> Co-Author: Dreamweaver MX: Instant Troubleshooter (August, 2003)
> Technical Editor: DMX 2004: The Complete Reference, DMX 2004: A
> Beginner's
> Guide, Mastering Macromedia Contribute
> Technical Reviewer: Dynamic Dreamweaver MX/DMX: Advanced PHP Web
> Development
>
>
>
>
> "tradmusic.com" <sales@NOSHPAMtradmusic.com> wrote in message
> news:ee3kel$ddd$1@forums.macromedia.com...
>> Hi,
>>
>> After a bit of twiddling, I changed the statement to this:
>>
>> SELECT *
>> FROM dbo.qryOrders
>> WHERE orderID IN (SELECT qryPaidOrders.orderID FROM qryPaidOrders)
>> ORDER BY orderdate ASC
>>
>> ...and it displays only those records who appear in the Paid Orders
>> table. If I change this to NOT IN, it displays only the orders in the
>> Orders table.
>>
>> Ideal, but I need to apply the customerID and vanopID variables....is
>> that possible within this one statement?
>>
>> Really appreciate the help,
>> Regards
>> Nath.
>>
>> "Nancy *Adobe Community Expert*" <nancy@webwish.com> wrote in message
>> news:ee3hhs$a08$1@forums.macromedia.com...
>>> You've added to it since the first post. Take out the customerID and
>>> vanopID parts and try focusing on just the orderID part. Get that
>>> working first.
>>>
>>> I would try seeing if you can select those orderID's that are IN the
>>> subquery first .. that would see whether or not the problem is the NOT
>>> .. which should work .. but let's see first whether the culprit is the
>>> query structure or not. See if you can select the orderID's that are in
>>> the paid orders table from the main query.
>>>
>>>
>>> --
>>> Nancy Gill
>>> Adobe Community Expert
>>> BLOG: http://www.dmxwishes.com/blog.asp
>>> Author: Dreamweaver 8 e-book for the DMX Zone
>>> Co-Author: Dreamweaver MX: Instant Troubleshooter (August, 2003)
>>> Technical Editor: DMX 2004: The Complete Reference, DMX 2004: A
>>> Beginner's
>>> Guide, Mastering Macromedia Contribute
>>> Technical Reviewer: Dynamic Dreamweaver MX/DMX: Advanced PHP Web
>>> Development
>>>
>>>
>>> "tradmusic.com" <sales@NOSHPAMtradmusic.com> wrote in message
>>> news:ee3gil$8u2$1@forums.macromedia.com...
>>>> Hi Nancy,
>>>>
>>>> Have just tried that, but it is still displaying records whose orderID
>>>> is also in a record in the Paid Orders table?
>>>>
>>>> I've got:
>>>>
>>>> SELECT *
>>>> FROM dbo.qryOrders
>>>> WHERE customerID = MMColParam OR vanopID = MMColParam1 AND orderID NOT
>>>> IN (SELECT qryPaidOrders.orderID FROM qryPaidOrders)
>>>> ORDER BY orderdate ASC
>>>>
>>>> Hope you can help.
>>>> Thanks. :o)
>>>> Nath.
>>>>
>>>>
>>>> "Nancy *Adobe Community Expert*" <nancy@webwish.com> wrote in message
>>>> news:ee3g4g$8bs$1@forums.macromedia.com...
>>>>> Why couldn't your inner query just be SELECT orderID from
>>>>> dbo.tblPaidOrders? Wouldn't the top 100% be all of them?
>>>>>
>>>>>
>>>>> --
>>>>> Nancy Gill
>>>>> Adobe Community Expert
>>>>> BLOG: http://www.dmxwishes.com/blog.asp
>>>>> Author: Dreamweaver 8 e-book for the DMX Zone
>>>>> Co-Author: Dreamweaver MX: Instant Troubleshooter (August, 2003)
>>>>> Technical Editor: DMX 2004: The Complete Reference, DMX 2004: A
>>>>> Beginner's
>>>>> Guide, Mastering Macromedia Contribute
>>>>> Technical Reviewer: Dynamic Dreamweaver MX/DMX: Advanced PHP Web
>>>>> Development
>>>>>
>>>>>
>>>>> "tradmusic.com" <sales@NOSHPAMtradmusic.com> wrote in message
>>>>> news:ee3c6e$3pt$1@forums.macromedia.com...
>>>>>> Hi,
>>>>>>
>>>>>> Two tables. Orders (orderID = PK) and Paid Orders (orderID = FK)
>>>>>> I want to display a recordset of orders from the Orders table where
>>>>>> the order ID doesn't appear in the Paid Orders table.
>>>>>>
>>>>>> I have tried something like this:
>>>>>>
>>>>>> SELECT *
>>>>>> FROM dbo.tblOrders
>>>>>> WHERE orderID NOT IN ( SELECT TOP 100 PERCENT tblPaidOrders.orderID
>>>>>> FROM dbo.tblPaidOrders )
>>>>>>
>>>>>> ...no joy with that though. Would appreciate some guidance. Thank
>>>>>> you.
>>>>>>
>>>>>> Regards
>>>>>> Nath.
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

I would think so .. now that you have the core statement working .. try
adding the other portions. Put the main statement in ( ) and add the
others. Like this:

SELECT *
FROM dbo.qryOrders
WHERE (orderID IN (SELECT qryPaidOrders.orderID FROM qryPaidOrders)
ORDER BY orderdate ASC) AND (customerid = MMColParam1) AND (vanopID =
MMColParam2)

See what that gives you and fiddle with those till you get what you want.


--
Nancy Gill
Adobe Community Expert
BLOG: http://www.dmxwishes.com/blog.asp
Author: Dreamweaver 8 e-book for the DMX Zone
Co-Author: Dreamweaver MX: Instant Troubleshooter (August, 2003)
Technical Editor: DMX 2004: The Complete Reference, DMX 2004: A Beginner's
Guide, Mastering Macromedia Contribute
Technical Reviewer: Dynamic Dreamweaver MX/DMX: Advanced PHP Web Development




"tradmusic.com" <sales@NOSHPAMtradmusic.com> wrote in message
news:ee3kel$ddd$1@forums.macromedia.com...
> Hi,
>
> After a bit of twiddling, I changed the statement to this:
>
> SELECT *
> FROM dbo.qryOrders
> WHERE orderID IN (SELECT qryPaidOrders.orderID FROM qryPaidOrders)
> ORDER BY orderdate ASC
>
> ...and it displays only those records who appear in the Paid Orders table.
> If I change this to NOT IN, it displays only the orders in the Orders
> table.
>
> Ideal, but I need to apply the customerID and vanopID variables....is that
> possible within this one statement?
>
> Really appreciate the help,
> Regards
> Nath.
>
> "Nancy *Adobe Community Expert*" <nancy@webwish.com> wrote in message
> news:ee3hhs$a08$1@forums.macromedia.com...
>> You've added to it since the first post. Take out the customerID and
>> vanopID parts and try focusing on just the orderID part. Get that
>> working first.
>>
>> I would try seeing if you can select those orderID's that are IN the
>> subquery first .. that would see whether or not the problem is the NOT ..
>> which should work .. but let's see first whether the culprit is the query
>> structure or not. See if you can select the orderID's that are in the
>> paid orders table from the main query.
>>
>>
>> --
>> Nancy Gill
>> Adobe Community Expert
>> BLOG: http://www.dmxwishes.com/blog.asp
>> Author: Dreamweaver 8 e-book for the DMX Zone
>> Co-Author: Dreamweaver MX: Instant Troubleshooter (August, 2003)
>> Technical Editor: DMX 2004: The Complete Reference, DMX 2004: A
>> Beginner's
>> Guide, Mastering Macromedia Contribute
>> Technical Reviewer: Dynamic Dreamweaver MX/DMX: Advanced PHP Web
>> Development
>>
>>
>> "tradmusic.com" <sales@NOSHPAMtradmusic.com> wrote in message
>> news:ee3gil$8u2$1@forums.macromedia.com...
>>> Hi Nancy,
>>>
>>> Have just tried that, but it is still displaying records whose orderID
>>> is also in a record in the Paid Orders table?
>>>
>>> I've got:
>>>
>>> SELECT *
>>> FROM dbo.qryOrders
>>> WHERE customerID = MMColParam OR vanopID = MMColParam1 AND orderID NOT
>>> IN (SELECT qryPaidOrders.orderID FROM qryPaidOrders)
>>> ORDER BY orderdate ASC
>>>
>>> Hope you can help.
>>> Thanks. :o)
>>> Nath.
>>>
>>>
>>> "Nancy *Adobe Community Expert*" <nancy@webwish.com> wrote in message
>>> news:ee3g4g$8bs$1@forums.macromedia.com...
>>>> Why couldn't your inner query just be SELECT orderID from
>>>> dbo.tblPaidOrders? Wouldn't the top 100% be all of them?
>>>>
>>>>
>>>> --
>>>> Nancy Gill
>>>> Adobe Community Expert
>>>> BLOG: http://www.dmxwishes.com/blog.asp
>>>> Author: Dreamweaver 8 e-book for the DMX Zone
>>>> Co-Author: Dreamweaver MX: Instant Troubleshooter (August, 2003)
>>>> Technical Editor: DMX 2004: The Complete Reference, DMX 2004: A
>>>> Beginner's
>>>> Guide, Mastering Macromedia Contribute
>>>> Technical Reviewer: Dynamic Dreamweaver MX/DMX: Advanced PHP Web
>>>> Development
>>>>
>>>>
>>>> "tradmusic.com" <sales@NOSHPAMtradmusic.com> wrote in message
>>>> news:ee3c6e$3pt$1@forums.macromedia.com...
>>>>> Hi,
>>>>>
>>>>> Two tables. Orders (orderID = PK) and Paid Orders (orderID = FK)
>>>>> I want to display a recordset of orders from the Orders table where
>>>>> the order ID doesn't appear in the Paid Orders table.
>>>>>
>>>>> I have tried something like this:
>>>>>
>>>>> SELECT *
>>>>> FROM dbo.tblOrders
>>>>> WHERE orderID NOT IN ( SELECT TOP 100 PERCENT tblPaidOrders.orderID
>>>>> FROM dbo.tblPaidOrders )
>>>>>
>>>>> ...no joy with that though. Would appreciate some guidance. Thank
>>>>> you.
>>>>>
>>>>> Regards
>>>>> Nath.
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Hi,

After a bit of twiddling, I changed the statement to this:

SELECT *
FROM dbo.qryOrders
WHERE orderID IN (SELECT qryPaidOrders.orderID FROM qryPaidOrders)
ORDER BY orderdate ASC

...and it displays only those records who appear in the Paid Orders table.
If I change this to NOT IN, it displays only the orders in the Orders table.

Ideal, but I need to apply the customerID and vanopID variables....is that
possible within this one statement?

Really appreciate the help,
Regards
Nath.

"Nancy *Adobe Community Expert*" <nancy@webwish.com> wrote in message
news:ee3hhs$a08$1@forums.macromedia.com...
> You've added to it since the first post. Take out the customerID and
> vanopID parts and try focusing on just the orderID part. Get that working
> first.
>
> I would try seeing if you can select those orderID's that are IN the
> subquery first .. that would see whether or not the problem is the NOT ..
> which should work .. but let's see first whether the culprit is the query
> structure or not. See if you can select the orderID's that are in the
> paid orders table from the main query.
>
>
> --
> Nancy Gill
> Adobe Community Expert
> BLOG: http://www.dmxwishes.com/blog.asp
> Author: Dreamweaver 8 e-book for the DMX Zone
> Co-Author: Dreamweaver MX: Instant Troubleshooter (August, 2003)
> Technical Editor: DMX 2004: The Complete Reference, DMX 2004: A
> Beginner's
> Guide, Mastering Macromedia Contribute
> Technical Reviewer: Dynamic Dreamweaver MX/DMX: Advanced PHP Web
> Development
>
>
> "tradmusic.com" <sales@NOSHPAMtradmusic.com> wrote in message
> news:ee3gil$8u2$1@forums.macromedia.com...
>> Hi Nancy,
>>
>> Have just tried that, but it is still displaying records whose orderID is
>> also in a record in the Paid Orders table?
>>
>> I've got:
>>
>> SELECT *
>> FROM dbo.qryOrders
>> WHERE customerID = MMColParam OR vanopID = MMColParam1 AND orderID NOT IN
>> (SELECT qryPaidOrders.orderID FROM qryPaidOrders)
>> ORDER BY orderdate ASC
>>
>> Hope you can help.
>> Thanks. :o)
>> Nath.
>>
>>
>> "Nancy *Adobe Community Expert*" <nancy@webwish.com> wrote in message
>> news:ee3g4g$8bs$1@forums.macromedia.com...
>>> Why couldn't your inner query just be SELECT orderID from
>>> dbo.tblPaidOrders? Wouldn't the top 100% be all of them?
>>>
>>>
>>> --
>>> Nancy Gill
>>> Adobe Community Expert
>>> BLOG: http://www.dmxwishes.com/blog.asp
>>> Author: Dreamweaver 8 e-book for the DMX Zone
>>> Co-Author: Dreamweaver MX: Instant Troubleshooter (August, 2003)
>>> Technical Editor: DMX 2004: The Complete Reference, DMX 2004: A
>>> Beginner's
>>> Guide, Mastering Macromedia Contribute
>>> Technical Reviewer: Dynamic Dreamweaver MX/DMX: Advanced PHP Web
>>> Development
>>>
>>>
>>> "tradmusic.com" <sales@NOSHPAMtradmusic.com> wrote in message
>>> news:ee3c6e$3pt$1@forums.macromedia.com...
>>>> Hi,
>>>>
>>>> Two tables. Orders (orderID = PK) and Paid Orders (orderID = FK)
>>>> I want to display a recordset of orders from the Orders table where the
>>>> order ID doesn't appear in the Paid Orders table.
>>>>
>>>> I have tried something like this:
>>>>
>>>> SELECT *
>>>> FROM dbo.tblOrders
>>>> WHERE orderID NOT IN ( SELECT TOP 100 PERCENT tblPaidOrders.orderID
>>>> FROM dbo.tblPaidOrders )
>>>>
>>>> ...no joy with that though. Would appreciate some guidance. Thank
>>>> you.
>>>>
>>>> Regards
>>>> Nath.
>>>>
>>>
>>>
>>
>>
>
>

You've added to it since the first post. Take out the customerID and
vanopID parts and try focusing on just the orderID part. Get that working
first.

I would try seeing if you can select those orderID's that are IN the
subquery first .. that would see whether or not the problem is the NOT ..
which should work .. but let's see first whether the culprit is the query
structure or not. See if you can select the orderID's that are in the paid
orders table from the main query.


--
Nancy Gill
Adobe Community Expert
BLOG: http://www.dmxwishes.com/blog.asp
Author: Dreamweaver 8 e-book for the DMX Zone
Co-Author: Dreamweaver MX: Instant Troubleshooter (August, 2003)
Technical Editor: DMX 2004: The Complete Reference, DMX 2004: A Beginner's
Guide, Mastering Macromedia Contribute
Technical Reviewer: Dynamic Dreamweaver MX/DMX: Advanced PHP Web Development


"tradmusic.com" <sales@NOSHPAMtradmusic.com> wrote in message
news:ee3gil$8u2$1@forums.macromedia.com...
> Hi Nancy,
>
> Have just tried that, but it is still displaying records whose orderID is
> also in a record in the Paid Orders table?
>
> I've got:
>
> SELECT *
> FROM dbo.qryOrders
> WHERE customerID = MMColParam OR vanopID = MMColParam1 AND orderID NOT IN
> (SELECT qryPaidOrders.orderID FROM qryPaidOrders)
> ORDER BY orderdate ASC
>
> Hope you can help.
> Thanks. :o)
> Nath.
>
>
> "Nancy *Adobe Community Expert*" <nancy@webwish.com> wrote in message
> news:ee3g4g$8bs$1@forums.macromedia.com...
>> Why couldn't your inner query just be SELECT orderID from
>> dbo.tblPaidOrders? Wouldn't the top 100% be all of them?
>>
>>
>> --
>> Nancy Gill
>> Adobe Community Expert
>> BLOG: http://www.dmxwishes.com/blog.asp
>> Author: Dreamweaver 8 e-book for the DMX Zone
>> Co-Author: Dreamweaver MX: Instant Troubleshooter (August, 2003)
>> Technical Editor: DMX 2004: The Complete Reference, DMX 2004: A
>> Beginner's
>> Guide, Mastering Macromedia Contribute
>> Technical Reviewer: Dynamic Dreamweaver MX/DMX: Advanced PHP Web
>> Development
>>
>>
>> "tradmusic.com" <sales@NOSHPAMtradmusic.com> wrote in message
>> news:ee3c6e$3pt$1@forums.macromedia.com...
>>> Hi,
>>>
>>> Two tables. Orders (orderID = PK) and Paid Orders (orderID = FK)
>>> I want to display a recordset of orders from the Orders table where the
>>> order ID doesn't appear in the Paid Orders table.
>>>
>>> I have tried something like this:
>>>
>>> SELECT *
>>> FROM dbo.tblOrders
>>> WHERE orderID NOT IN ( SELECT TOP 100 PERCENT tblPaidOrders.orderID FROM
>>> dbo.tblPaidOrders )
>>>
>>> ...no joy with that though. Would appreciate some guidance. Thank you.
>>>
>>> Regards
>>> Nath.
>>>
>>
>>
>
>

Hi Nancy,

Have just tried that, but it is still displaying records whose orderID is
also in a record in the Paid Orders table?

I've got:

SELECT *
FROM dbo.qryOrders
WHERE customerID = MMColParam OR vanopID = MMColParam1 AND orderID NOT IN
(SELECT qryPaidOrders.orderID FROM qryPaidOrders)
ORDER BY orderdate ASC

Hope you can help.
Thanks. :o)
Nath.


"Nancy *Adobe Community Expert*" <nancy@webwish.com> wrote in message
news:ee3g4g$8bs$1@forums.macromedia.com...
> Why couldn't your inner query just be SELECT orderID from
> dbo.tblPaidOrders? Wouldn't the top 100% be all of them?
>
>
> --
> Nancy Gill
> Adobe Community Expert
> BLOG: http://www.dmxwishes.com/blog.asp
> Author: Dreamweaver 8 e-book for the DMX Zone
> Co-Author: Dreamweaver MX: Instant Troubleshooter (August, 2003)
> Technical Editor: DMX 2004: The Complete Reference, DMX 2004: A
> Beginner's
> Guide, Mastering Macromedia Contribute
> Technical Reviewer: Dynamic Dreamweaver MX/DMX: Advanced PHP Web
> Development
>
>
> "tradmusic.com" <sales@NOSHPAMtradmusic.com> wrote in message
> news:ee3c6e$3pt$1@forums.macromedia.com...
>> Hi,
>>
>> Two tables. Orders (orderID = PK) and Paid Orders (orderID = FK)
>> I want to display a recordset of orders from the Orders table where the
>> order ID doesn't appear in the Paid Orders table.
>>
>> I have tried something like this:
>>
>> SELECT *
>> FROM dbo.tblOrders
>> WHERE orderID NOT IN ( SELECT TOP 100 PERCENT tblPaidOrders.orderID FROM
>> dbo.tblPaidOrders )
>>
>> ...no joy with that though. Would appreciate some guidance. Thank you.
>>
>> Regards
>> Nath.
>>
>
>

Why couldn't your inner query just be SELECT orderID from dbo.tblPaidOrders?
Wouldn't the top 100% be all of them?


--
Nancy Gill
Adobe Community Expert
BLOG: http://www.dmxwishes.com/blog.asp
Author: Dreamweaver 8 e-book for the DMX Zone
Co-Author: Dreamweaver MX: Instant Troubleshooter (August, 2003)
Technical Editor: DMX 2004: The Complete Reference, DMX 2004: A Beginner's
Guide, Mastering Macromedia Contribute
Technical Reviewer: Dynamic Dreamweaver MX/DMX: Advanced PHP Web Development


"tradmusic.com" <sales@NOSHPAMtradmusic.com> wrote in message
news:ee3c6e$3pt$1@forums.macromedia.com...
> Hi,
>
> Two tables. Orders (orderID = PK) and Paid Orders (orderID = FK)
> I want to display a recordset of orders from the Orders table where the
> order ID doesn't appear in the Paid Orders table.
>
> I have tried something like this:
>
> SELECT *
> FROM dbo.tblOrders
> WHERE orderID NOT IN ( SELECT TOP 100 PERCENT tblPaidOrders.orderID FROM
> dbo.tblPaidOrders )
>
> ...no joy with that though. Would appreciate some guidance. Thank you.
>
> Regards
> Nath.
>