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.
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
7
Replies
AdChoices