You are sort of correct that the solution will require two stages. However,
that does not translate into two trips to the database. There is a special
type of JOIN that is relevant for this task: the outer join. Since you are
using Access, you should look it up in online help since this example will
only utilize one aspect of this type of join.

This example will also utilize a "derived" or "virtual" table, which is
defined as a table, a set of rows, derived as the result of a select
statement. The derived table can be used in the FROM clause of another
query. There are two ways this can be accomplished. The first that I will
demonstrate utilizes a saved query created using the Access Query Builder.
Open your database in Access and switch to the Queries tab. Then
double-click the item in the window that says "Create New Query in Design
View". Close the Choose Tables dialog without selecting a table, switch to
SQL View and paste this sql statement into the window:

SELECT GroupID FROM
tblGroupsContacts
WHERE ContactID = [pContactID]

Run the query, entering 1 when you are prompted for a value for
[pContactID]. You should see a list of GroupIDs to which ContactID 1
belongs. Click the Save toolbar button (or do File|Save from the menu) and
save this query as "qGroupsForSpecifiedID".

Now create another query in Design View, again switching to SQL View without
selecting any tables and paste this sql statement in:

SELECT g.GroupID, GroupName
FROM tblGroups As g LEFT OUTER JOIN
qGroupsForSpecifiedIDAs q
ON g.GroupID = q.GroupID
WHERE q.GroupID Is Null

Run this query, again supplying 1 when prompted. You should see your desired
result.

The other way to do this is to use a subquery, like this (assumes Access
2000 or later):

SELECT g.GroupID, GroupName
FROM tblGroups As g LEFT OUTER JOIN
(SELECT GroupID FROM
tblGroupsContacts
WHERE ContactID = [pContactID]) As q
ON g.GroupID = q.GroupID
WHERE q.GroupID Is Null

Which version should you use? Well, it depends on whether or not you intend
to re-use the sql used in the subquery. If this is the only place it will be
needed, then use the second (subquery) version. If you see other
applications for the sql in the subquery, then you should put it in the
saved query so it can be reused whenever you need it for other queries,
without having to rewrite it.

In either case, you should save the final version as
"qGroupsNotJoinedBySpecifiedContact" so you can call it from ASP like this:

contactid = clng(1)
Set rs=createobject("adodb.recordset")
conn.qGroupsNotJoinedBySpecifiedContact contactid , rs
If not rs.EOF then
'process the array - I recommend using GetRows*
else
'no records were returned
end if

Here is more information about using this technique to run saved parameter
queries from asp:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

and this illustrates why I recommend using saved parameter queries instead
of dynamic sql:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

OK, I've shown you how to achieve your specific requirement: "I want my sql
to find (for a particular ContactID)the group names of
the groups they are not a member of. ie Hockey and Netball"
Hopefully you'll be able to build on this to retrieve other data you need
for your page without making multiple trips to your database.

Bob Barrows
*Should I use recordset iteration, or GetRows(), or GetString()?
http://www.aspfaq.com/show.asp?id=2467


[quoted text, click to view]


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Hi Bob

Thanks for your detailed answer.

I will have a go at implementing it tonight or Wednesday.

Thanks ColinK

Hi Bob

Thanks for your help. I got the Optiion 1 Access queries working fine.

I did some research into Get Rows, but cannot implement on my page.

I tried some Do, While, loops but always get 1 wrong record for any
given ID, rather than up to 5.

I changed Conn.P..... to AdoCon.P... to match my existing ado
connection.

I addded Dim rs

I would really appreciate your help with processing the array.

I want <%Response.Write (rs("GroupName"))%> to print out the relevant
rows.

contactid =3D clng(1)
Set rs=3Dcreateobject("adodb.records=ADet")
adoCon.qGroupsNotJoinedBySpecifi=ADedContact contactid , rs
If not rs.EOF then
'process the array - I recommend using GetRows*
else=20
'no records were returned=20
end if

I can't do this blind. Please show me the result of:

<%
'open the recordset, then:
if not rs.eof then
response.write "<table><tr>"
for each fld in rs.Fields
response.write "<th>" & fld.name & "</th>"
next
response.write "</tr><tr><td>"
sHTML= rs.getstring(,, "</td><td>","</td></tr><tr><td>","~null~")
sHTML=left(sHTML,len(sHTML) - 8)
response.write sHTML & "</table>"
end if
rs.close:set rs=nothing
AdoCon.close:set AdoCon = nothing
%>

Run the page, View Source, copy the html and paste it into your reply

Then explain how you want the returned results to be processed. Perhaps you
could rewrite the html so that it looks the way you want it to look and show
that to e.

Bob Barrows

[quoted text, click to view]

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Hi Bob

Your suggestion worked nearly perfectly, the loop need modified
slightly to get the 2 related fields to print beside each other.

However, my original attempt at processing the recordset was OK, the
problem was my naming of ContactID and rsContactID.

With regard to arrays and recordsets (as per your other contribution
today), you are correct it is an a recordset not an array; if I
understand the difference correctly!

By now you are very aware that I am on a steep learning curve.


Is this correct?

An array can contain exactly the same data as a recordset. Both are
evffectively tables; however an array is indexed so that any individual
element (field) can be individually referenced, where a record set must
be procesed as a whole. Even if I am correct I am sure it could be
worded better.

Thanks ColinK

[quoted text, click to view]


For starters, if you do not have the vbscript documentation, you should get
it now from:
http://tinyurl.com/7rk6
This download will also contain the documentation for jscript and for the
scripting host components: Regular Expressions and FileSystemObject


An array is a data structure that is intrinsic to vbscript (builtin). It
knows nothing about databases or tables or anything except how many
dimensions and how many pieces of data are contained in it. So that's the
first of its differences from a recordset. Another difference is that a
recordset (unless it's a rarely used hierarchical recordset) is strictly
two-dimensional: columns and rows (fields and records). An array can be
created with ahy number of dimensions, including a single dimension:

ar = array(1,2,3)

The only means of accessing any of the data elements in the array is by use
of the array's index. There is no metadata (data about data) stored in an
array.

On the other hand, a recordset, which is a COM object supplied by the ADODB
COM library, is a cursor engine used to work with data from databases*.
Since it is a cursor engine, many things are easily done with a recordset
that are very difficult to do with arrays : filtering, sorting, etc.** As a
COM object, a recordset is a class which consists of various properties,
collections and methods all of which enable the developer to accomplish
various tasks. The recordset is a large bulky object compared to an array
due to its need to contain metadata: field names, types, sizes, underlying
values, etc. as well as all the things a cursor engine needs to make
navigation through the recordset possible.

Bob Barrows


*Ad hoc recordsets, i.e., recordsets created from scratch and populated
without ever being connected to a database, are possible, but rarely used.
For the most part, when you see a recordset in somebody's code, it exists as
the result of retrieving some data from a database.

**I am talking only about VB/VBA/vbscript arrays here. I am aware that
arrays created by other languages, such as jscript, have builtin methods for
sorting, etc.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"