Very Computer

I am developing a web site which needs to query a table
for DISTINCT product IDs the result of such query then
needs to be JOINED with a product details table.  I am
using VBSCRIPT and an ACCESS database.  How do I
accomplish this?  Any help would be greatly appreciated.

Can you post the CREATE TABLE statements, a few sample data and the
expected results as a list?

--
- Anith
(Please respond only to newsgroups)

Here is the first query which finds me unique catalog ids
in a table.  Basically, I have a table of watch styles.
These include "FUN", "COOL", "HITECH", "ELEGANT" and so
forth. I have another table of WATCHES which include the
catalogid and other detailed information about each WATCH
item.  I then have another table which establishes the
relationship between a watch catalogid and a styleid.  In
other words, any particular watch could be "FUN", "COOL"
and "HITECH".  So therefore the watchesstyles table is a
two column table consisting of catalogids and styleids
relating each watch to the styles it is considered to be.

I need a browser on the internet to look for all watches
that are "COOL" and/or "FUN".  I need to find the DISTINCT
catalogids which meet that criteria.  I then need to join
the results of that query with my detailed description of
each watch in order to present the browser with detailed
product information.  

1. QUERY1 - Find catalogids for qualifying styles.

SELECT DISTINCT watchesstyles.catalogid
FROM watchesstyles
WHERE (((watchesstyles.styleid)=5))

2. Take the results of QUERY1 and join it with the watch
details table.

SELECT Query1.*, watches.*
FROM Query1 INNER JOIN watches ON Query1.catalogid =
watches.catalogid

How do I do this with VBScript commands?

Here is how I normally pull data out:

If IsObject(Session("mmwatches_conn")) Then
    Set conn = Session("mmwatches_conn")
Else
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.open "mmwatches","admin",""
    Set Session("??mmwatches_conn") = conn
End If
    sql = "SELECT DISTINCT watchesstyles.catalogid FROM
watchesstyles WHERE (((watchesstyles.styleid)=5)) "
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open sql, conn, 3, 3
If rs.eof Then
    rs.AddNew
End If

Thanks in advance for your help.
Tom Shubert

Quote:>-----Original Message-----
>Can you post the CREATE TABLE statements, a few sample
data and the
>expected results as a list?

>--
>- Anith
>(Please respond only to newsgroups)

>.

SELECT *                    --- use column names
  FROM Watches
 INNER JOIN (SELECT catalogid
               FROM watchesstyles
              WHERE styleId = 5) D(catalogId)    -- derived table
    ON Watches.catalogid = D.catalogid

--
- Anith
(Please respond only to newsgroups)

I really need to do a two step processs.  Get my DISTINCT
catalogids from the styles query.  Then JOIN these RESULTS
with the watches table.  I simply do not know how to do
this.

Please help.
Thanks

Quote:>-----Original Message-----
>In T-SQL you can do:

>SELECT *                    --- use column names
>  FROM Watches
> INNER JOIN (SELECT catalogid
>               FROM watchesstyles
>              WHERE styleId = 5) D(catalogId)    --
derived table
>    ON Watches.catalogid = D.catalogid

>--
>- Anith
>(Please respond only to newsgroups)

>.

Tom Shubert,

Quote:> That does not work for me because when my criteria might
> be more than one styleid, I will get duplicate watch
> records and I only want a single occurance for any given
> catalogid regardless of how many different styles it is
> considered to be.

> I really need to do a two step processs.  Get my DISTINCT
> catalogids from the styles query.  Then JOIN these RESULTS
> with the watches table.  I simply do not know how to do
> this.

SELECT *    
FROM Watches
INNER JOIN (SELECT DISTINCT catalogid
            FROM watchesstyles
            WHERE styleId = 5) D(catalogId)
ON Watches.catalogid = D.catalogid

If this is not what you need, then post DDL (create table ...),
sample data in the form of insert statements, and desired output.

Linda

Linda:

That lloks great.  I have a couple questions.  First, if I
have a list of styleids, can I use an IN operator as
follows:

SELECT *    
FROM Watches
INNER JOIN (SELECT DISTINCT catalogid
            FROM watchesstyles
            WHERE styleId IN (5,6,7)) D(catalogId)
ON Watches.catalogid = D.catalogid

Also, I am not clear what the "D(catalogid)" does.  Can
you explain that?

Thanks a million,
Tom

Quote:>-----Original Message-----
>Tom Shubert,

>> That does not work for me because when my criteria might
>> be more than one styleid, I will get duplicate watch
>> records and I only want a single occurance for any given
>> catalogid regardless of how many different styles it is
>> considered to be.

>> I really need to do a two step processs.  Get my
DISTINCT
>> catalogids from the styles query.  Then JOIN these
RESULTS
>> with the watches table.  I simply do not know how to do
>> this.

>You can select distinct values in the derived table.

>SELECT *    
>FROM Watches
>INNER JOIN (SELECT DISTINCT catalogid
>            FROM watchesstyles
>            WHERE styleId = 5) D(catalogId)
>ON Watches.catalogid = D.catalogid

>If this is not what you need, then post DDL (create
table ...),
>sample data in the form of insert statements, and desired
output.

>Linda

>.

Yes, of course, you can use a set of values using IN list in
your WHERE clause.

D is nothing but an alias for a derived table. catalogid is
the column name (optional) in the derived table. See details
about derived tables in SQL Server Books Online.

--
- Anith
(Please respond only to newsgroups)

OK.  I do not mean to be so terribly inept, however, I am
having a mental problem grasping how to combine the
excellent answer you all have given me with the
following.  My query syntax that I need JOINED to the
distinct selection of catalogids from the watchesstyles
table is this:

SELECT DISTINCT watches.*, sizes.*, brands.*
FROM brands INNER JOIN (sizes INNER JOIN watches ON
sizes.sizeid = watches.size) ON brands.brandid =
watches.brandid

So where in all of this mess do I add the IONNER JOIN to
watchstyles:

SELECT DISTINCT catalogid
            FROM watchesstyles
            WHERE styleId IN (5,6,7))

Sorry for being so confused.  I am running out of time on
figuring this out.  The underlying data source is an
ACCESS database on a web server and I am coding server-
side VBScript to display results to inquiring browsers.

Thanks in advance.  I really appreciate everyones
thoughtful help.
Tom

Quote:>-----Original Message-----
>Yes, of course, you can use a set of values using IN list
in
>your WHERE clause.

>D is nothing but an alias for a derived table. catalogid
is
>the column name (optional) in the derived table. See
details
>about derived tables in SQL Server Books Online.

>--
>- Anith
>(Please respond only to newsgroups)

>.

SELECT DISTINCT watches.*, sizes.*, brands.*, D. * --use column names
  FROM brands
 INNER JOIN watches
    ON brands.brandid = watches.brandid
 INNER JOIN sizes
    ON sizes.sizeid = watches.size
 INNER JOIN (SELECT DISTINCT catalogid
               FROM watchesstyles
              WHERE styleId IN (5,6,7)) D (catalogid)
    ON Watches.catalogid = D.catalogid

--
- Anith
(Please respond only to newsgroups)

Thanks.  I am getting closer I think.  But I put this in
and I got a "missing operator" error.  Also, I am not
clear about the "D. *" and the "--use column names"
comments.  I am testing this by going into Access 2002 and
making a new query in SQL view but I cannot get it to
work.  Any other help would be most appreciated.

Thanks again,
Tom

Quote:>-----Original Message-----
>Here is my try:

>SELECT DISTINCT watches.*, sizes.*, brands.*, D. * --use
column names
>  FROM brands
> INNER JOIN watches
>    ON brands.brandid = watches.brandid
> INNER JOIN sizes
>    ON sizes.sizeid = watches.size
> INNER JOIN (SELECT DISTINCT catalogid
>               FROM watchesstyles
>              WHERE styleId IN (5,6,7)) D (catalogid)
>    ON Watches.catalogid = D.catalogid

>--
>- Anith
>(Please respond only to newsgroups)

>.

Let me try :-)

Using -- means, it is a one line comment in T-SQL. For e.g.

-- This is a comment

This is also equivalent to /*...*/ which can be applied to multiple lines.
For e.g.

/* This is a comment */

For more details on comments and how it is used in T-SQL, pl. refer to
SQL Server Books Online.

Generally, using * in a SELECT statement, like in SELECT * FROM tbl or
SELECT t1.* FROM tbl t1 etc. are considered bad programming practice.
You can search the .programming Newsgroup archives for this reasoning.

So to put it correctly, your query should be like:

SELECT DISTINCT watches.col1, watches.col2,...sizes.col1,...D.categoryid
  FROM ....

Also the error is due to the space between D. and * in my posted query.

Hope this helps..

--
- Anith
(Please respond only to newsgroups)