|
 |
asp_databases thread: Sub Queries - how to build from users form entries
Message #1 by "Colin Montgomery" <colin.montgomery@c...> on Wed, 22 Aug 2001 14:04:36
|
|
Hi
I've been trying to figure out how to build a query in VBScript when one
of the search fields is in a sub table.
To simplify: lets say i've got 3 fields in my search form: CustomerName,
CustomerType and OrderType. I want to be able to create a query on the fly
which will correctly return all records which have CustomerName = MyName,
CustomerType = MyCustType (both from tblCustomers) and also WHERE
OrderType = MyOrderType (from tblOrders). The join will be
tblCustomers.CustomerID = tblOrders.fkCustomerID.
I've tried various forms of Sub-queries, but none seem to be right.
Hope someone can help.
Colin
Message #2 by David Cameron <dcameron@i...> on Thu, 23 Aug 2001 09:32:56 +1000
|
|
SELECT <Fields>
FROM tblCustomers
INNER JOIN tblOrders ON
tblOrders.fkCustomerID = tblCustomers.CustomerID
WHERE tblCustomers.CustomerName = <value1>
AND tblCustomers.CustomerType = <value2>
AND tblOrders.MyOrderType = <value3>
you may want to look into using LIKE for the customer name.
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: Colin Montgomery [mailto:colin.montgomery@c...]
Sent: Thursday, 23 August 2001 12:05 AM
To: ASP Databases
Subject: [asp_databases] Sub Queries - how to build from users form
entries
Hi
I've been trying to figure out how to build a query in VBScript when one
of the search fields is in a sub table.
To simplify: lets say i've got 3 fields in my search form: CustomerName,
CustomerType and OrderType. I want to be able to create a query on the fly
which will correctly return all records which have CustomerName = MyName,
CustomerType = MyCustType (both from tblCustomers) and also WHERE
OrderType = MyOrderType (from tblOrders). The join will be
tblCustomers.CustomerID = tblOrders.fkCustomerID.
I've tried various forms of Sub-queries, but none seem to be right.
Hope someone can help.
Colin
Message #3 by Colin.Montgomery@C... on Thu, 23 Aug 2001 10:01:25 +0100
|
|
thanks David - appreciate your help
I am using LIKE for CustomerName, but thanks for pointing it out anyway.
-----Original Message-----
From: David Cameron [mailto:dcameron@i...]
Sent: 23 August 2001 00:33
To: ASP Databases
Subject: [asp_databases] RE: Sub Queries - how to build from users form
en tries
SELECT <Fields>
FROM tblCustomers
INNER JOIN tblOrders ON
tblOrders.fkCustomerID = tblCustomers.CustomerID
WHERE tblCustomers.CustomerName = <value1>
AND tblCustomers.CustomerType = <value2>
AND tblOrders.MyOrderType = <value3>
you may want to look into using LIKE for the customer name.
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: Colin Montgomery [mailto:colin.montgomery@c...]
Sent: Thursday, 23 August 2001 12:05 AM
To: ASP Databases
Subject: [asp_databases] Sub Queries - how to build from users form
entries
Hi
I've been trying to figure out how to build a query in VBScript when one
of the search fields is in a sub table.
To simplify: lets say i've got 3 fields in my search form: CustomerName,
CustomerType and OrderType. I want to be able to create a query on the fly
which will correctly return all records which have CustomerName = MyName,
CustomerType = MyCustType (both from tblCustomers) and also WHERE
OrderType = MyOrderType (from tblOrders). The join will be
tblCustomers.CustomerID = tblOrders.fkCustomerID.
I've tried various forms of Sub-queries, but none seem to be right.
Hope someone can help.
Colin
|
|
|
View Cart
