ASP -- INNER JOIN

I've got an Access 2000 database on my webserver and I'm trying to write a
SQL command to query the database and pull out cheese information. I can't
get the INNER JOIN statement to work properly. What I'm trying to do is
have the user click on a cheese milk type, let's say Cow Milk and then have
a query run against the database that will display all the countries that
have cheeses of that type, but not to have duplicate country names in it.

For example, I've got sample tables below:

Table_Cheese:
Cheese, Milk, CountryID
Cheddar, Cow, 1
Monterey, Cow, 1
Colby, Goat, 1
Blue, Cow, 2

Table_Country:
CountryId, Name
1, USA
2, France

SELECT table_cheese.* from table_cheese where milk = 'Cow' order by
CountryId
response.write CountryID

This code would produce the follow output:
1
1
2

What I'd like is the country name to be displayed and duplicates to be
eliminated. Output should look like this:
France
USA

I don't get any results when I run this in Access and I get an error when I
try to run it in .ASP:
SELECT table_cheese.cheese, table_cheese.milk, table_country.name
FROM table_cheese INNER JOIN table_country ON
table_country.name =table_cheese.countryID
WHERE milk = 'Cow'

Thanks

Re: ASP -- INNER JOIN

TRB_NV wrote:
> I don't get any results when I run this in Access and I get an error
> when I try to run it in .ASP:

Why would you try to run it from ASP if it wouldn't run in Access?

> SELECT table_cheese.cheese, table_cheese.milk, table_country.name

If you only want the country names, why are you requesting those other
columns?

> FROM table_cheese INNER JOIN table_country ON
> table_country.name =table_cheese.countryID

Ummm, when is the name ever going to equal the countryID?
Answer: never. Try it like this:

SELECT DISTINCT table_country.name
FROM table_cheese INNER JOIN table_country ON
table_country.countryID=table_cheese.countryID
WHERE milk = 'Cow'


--
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.

Re: ASP -- INNER JOIN

Thanks Bob for the humor...I keep laughing now when I can't get it to work
in Access, I don't even bother trying to run it from ASP. It really is
pointless and I have no idea why I was doing it. Must've been too late at
night and my brain wasn't thinking clearly.

Also thanks for your very clear and concise example. Seeing how you had it
set up and the logic behind it had me write the production code 1-2-3.

"Bob Barrows [MVP]" wrote in message
news:ucs8MnQVGHA.5172@TK2MSFTNGP12.phx.gbl...
> TRB_NV wrote:
>> I don't get any results when I run this in Access and I get an error
>> when I try to run it in .ASP:
>
> Why would you try to run it from ASP if it wouldn't run in Access?
>
>> SELECT table_cheese.cheese, table_cheese.milk, table_country.name
>
> If you only want the country names, why are you requesting those other
> columns?
>
>> FROM table_cheese INNER JOIN table_country ON
>> table_country.name =table_cheese.countryID
>
> Ummm, when is the name ever going to equal the countryID?
> Answer: never. Try it like this:
>
> SELECT DISTINCT table_country.name
> FROM table_cheese INNER JOIN table_country ON
> table_country.countryID=table_cheese.countryID
> WHERE milk = 'Cow'
>
>
> --
> 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.
>
>