VB & Database, Using ado recordset in an SQL Server inner join

Terrence Reddic
#1 / 4

Using ado recordset in an SQL Server inner join

Hello,

I am using VB6, querying an SQL7 database via ADO 2.1.

I have the following schema::
Table: Companys
Fields: CompID, CompName, ...

Table: Transactions
Fields: CompID, TransactionID, ClientID, ...

Table: Clients
Fields: ClientID, LastName, Firstname, ...

I need to perform an inner join between these 3 tables, but only return the
results associated with one row on the primary Companys table. The results
of the inner join will be records with the following fields, limited to the
desired company (CompID = 123):

CompID, CompName, TransactionID, ClientID, LastName, Firstname

To solve the problem, I would like to include a "WHERE" clause against the
Companys table in the inner join - but that is not legal.

Optionally, I would be satisfied applying a "FILTER" to the Companys table.
This would serve the same purpose as a "WHERE" clause. The only problem is
that FILTERs are only applied to the ADO recordset, not the actual SQL
server database table! Since the inner join will hit the actual, un-FILTERED
tables, this approach will not work as needed!

THEREFORE, I would like to know if it is possible to use a recordset
(properly WHEREd or FILTERed) as a source table in an inner join. The
desired query would resemble the following:

----------------------------------------------------------------------------
---------------------------
Dim rsCompany As New ADODB.Recordset ' single record from
the Companys database
Dim rsCompTranClient As New ADODB.Recordset ' result of inner join
operation
Dim CurrentCompany as Integer '
Desired Company

CurrentCompany = 123 ' CompID for desired company

with DataEnvironment1
rsCompany.ActiveConnection = .Connection1
rsCompany.Source = "Select * from Companys " & _
" where CompID = " & Str$(CurrentCompany) ' this
provides a 1 record recordset
rsCompany.Open

rsCompTranClient.ActiveConnection = .Connection1
rsCompTranClient.Source = & _
"select rsCompany("CompID"), rsCompany("CompName"), " &
_
"transactions.transactionid, transactions.clientid, " &
_
"clients.lastname, clients.firstname " & _
"from rsCompany inner join " & _
"(transactions inner join clients " & _
"on transactions.clientid = clients.clientid) " & _
"on rsCompany("CompID") = transactions.CompID"
rsCompTranClient.Open

if AskForPayRaise() = vbYes
TakeVacation()
end if
end with
----------------------------------------------------------------------------
---------------------------

If this approach is possible, I will be able to avoid using a parameterized
Shape, and then pulling apart a hierarchical record set. I have also
successfully developed a work-around involving building a 1 record table
using "Select CompID into Just1Company from Companys where CompanyID = 123",
and then using this table in the inner join in place of the full Companys
table. Naturally, this approach requires giving users Drop Table and Create
Table permissions - not an ideal solution.

Being able to use a recordset in an inner join would be an ideal solution!

Thanks in advance,

Terry

Thu, 12 Sep 2002 03:00:00 GMT

Roger Mort
#2 / 4

Using ado recordset in an SQL Server inner join

Quote:

> I have the following schema::
> Table: Companys
> Fields: CompID, CompName, ...

> Table: Transactions
> Fields: CompID, TransactionID, ClientID, ...

> Table: Clients
> Fields: ClientID, LastName, Firstname, ...

> I need to perform an inner join between these 3 tables, but only return the
> results associated with one row on the primary Companys table. The results
> of the inner join will be records with the following fields, limited to the
> desired company (CompID = 123):

> CompID, CompName, TransactionID, ClientID, LastName, Firstname

> To solve the problem, I would like to include a "WHERE" clause against the
> Companys table in the inner join - but that is not legal.

I must be missing something, because it seems to me you could just do

SELECT Companys.CompID, CompName, TransactionID, Transactions.ClientID,
LastName, Firstname
FROM Companys, Transactions, Clients
WHERE Companys.CompID = Transactions.CompID
AND Transactions.ClientID = Clients.ClientID

Roger Morton

Thu, 12 Sep 2002 03:00:00 GMT

John Gos
#3 / 4

Using ado recordset in an SQL Server inner join

Something I learned from "Inside SQL Server". I beleive you could do
something like this.

SELECT Companys.CompID, CompName, TransactionID, Transactions.ClientID,
LastName, Firstname
FROM Companys Inner Join Transactions On
Companys.CompID = Transactions.CompID And
Companys.CompID = 123
Inner Join Clients ON
Transactions.ClientID = Clients.ClientID

John Gose

Quote:

> Hello,

> I am using VB6, querying an SQL7 database via ADO 2.1.

> I have the following schema::
> Table: Companys
> Fields: CompID, CompName, ...

> Table: Transactions
> Fields: CompID, TransactionID, ClientID, ...

> Table: Clients
> Fields: ClientID, LastName, Firstname, ...

> I need to perform an inner join between these 3 tables, but only return
the
> results associated with one row on the primary Companys table. The results
> of the inner join will be records with the following fields, limited to
the
> desired company (CompID = 123):

> CompID, CompName, TransactionID, ClientID, LastName, Firstname

> To solve the problem, I would like to include a "WHERE" clause against the
> Companys table in the inner join - but that is not legal.

> Optionally, I would be satisfied applying a "FILTER" to the Companys
table.
> This would serve the same purpose as a "WHERE" clause. The only problem is
> that FILTERs are only applied to the ADO recordset, not the actual SQL
> server database table! Since the inner join will hit the actual,
un-FILTERED
> tables, this approach will not work as needed!

> THEREFORE, I would like to know if it is possible to use a recordset
> (properly WHEREd or FILTERed) as a source table in an inner join. The
> desired query would resemble the following:

> --------------------------------------------------------------------------
--
> ---------------------------
> Dim rsCompany As New ADODB.Recordset ' single record
from
> the Companys database
> Dim rsCompTranClient As New ADODB.Recordset ' result of inner
join
> operation
> Dim CurrentCompany as Integer '
> Desired Company

> CurrentCompany = 123 ' CompID for desired company

> with DataEnvironment1
> rsCompany.ActiveConnection = .Connection1
> rsCompany.Source = "Select * from Companys " & _
> " where CompID = " & Str$(CurrentCompany) '
this
> provides a 1 record recordset
> rsCompany.Open

> rsCompTranClient.ActiveConnection = .Connection1
> rsCompTranClient.Source = & _
> "select rsCompany("CompID"), rsCompany("CompName"), "
&
> _
> "transactions.transactionid, transactions.clientid, "
&
> _
> "clients.lastname, clients.firstname " & _
> "from rsCompany inner join " & _
> "(transactions inner join clients " & _
> "on transactions.clientid = clients.clientid) " & _
> "on rsCompany("CompID") = transactions.CompID"
> rsCompTranClient.Open

> if AskForPayRaise() = vbYes
> TakeVacation()
> end if
> end with
> --------------------------------------------------------------------------
--
> ---------------------------

> If this approach is possible, I will be able to avoid using a
parameterized
> Shape, and then pulling apart a hierarchical record set. I have also
> successfully developed a work-around involving building a 1 record table
> using "Select CompID into Just1Company from Companys where CompanyID =
123",
> and then using this table in the inner join in place of the full Companys
> table. Naturally, this approach requires giving users Drop Table and
Create
> Table permissions - not an ideal solution.

> Being able to use a recordset in an inner join would be an ideal solution!

> Thanks in advance,

> Terry

Fri, 13 Sep 2002 03:00:00 GMT

Phil Turtl
#4 / 4

Using ado recordset in an SQL Server inner join

Terrence,

The 'standard' way to do this is to use the key relationships in the JOIN
clause and put the 'filter' stuff in the WHERE clause. This way you're
separating the relationships between tables from the restrictions you're
putting on the selections. It probably makes it easier for the SQL parser as
well as anybody who has to maintain the code.

I'd recommend a good basic database/SQL book - no need to look for 'Inside
anything' type books, there's no big secret here - just a good coverage of
standard SQL 92.

--
Phil Turtle