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

There is nothing illegal about using a Where clause along with an Inner Join
clause in the same sql statement.

Select the fields from the joined tables and apply a Where filter:

Select Companys.CompID, Companys.CompName, Transactions.TransactionID,
Transactions.ClientID, Clients.LastName, Clients.FirstName From Companys
Inner Join (Transactions Inner Join Clients On Transactions.ClientID =
Clients.ClientID) On Companys.CompID= Transactions.CompID Where
Companys.CompID = 123

Of course, since I don't have access to your database, this is off the top
of my head,

Kerry Moorman