3 table join help needed (I have searched but...)

i am still at a loss from after all my time searching for a solution to
this query (litteraly). Here are my tables

featured_wine
=============================
ID wine regionID countryID
1 thunderbird 1 1


featured_wine_country
==============================
countryID country
1 usa
2 italy


featured_wine_region
==============================
regionID region
1 napa
2 tuscony


I want my recordset to display...

wine: thunderbird
region: napa
country: usa

rather than

wine: thunderbird
region: 1
country: 1

Here is a select statement that I am playing with...

sqlText = "SELECT
featured_wine.wine,featured_wine.regionID,featured_wine.coun tryID FROM
featured_wine INNER JOIN featured_wine_country ON
(featured_wine_country.countryID=featured_wine.countryID) AND INNER
JOIN featured_wine_region ON
(featured_wine_region.regionID=featured_wine.regionID) WHERE blah,
blah, blah...

The above did not work. I amable to get a 2 table join to work well,
but adding the third table throws me off.

This is a where I'd like to turn the 'learning curve' folks.

Thanks

Re: 3 table join help needed (I have searched but...)

wrote in message
news:1134587636.625924.13020@o13g2000cwo.googlegroups.com...
> i am still at a loss from after all my time searching for a solution to
> this query (litteraly). Here are my tables
>
> featured_wine
> =============================
> ID wine regionID countryID
> 1 thunderbird 1 1
>
>
> featured_wine_country
> ==============================
> countryID country
> 1 usa
> 2 italy
>
>
> featured_wine_region
> ==============================
> regionID region
> 1 napa
> 2 tuscony
>
>
> I want my recordset to display...
>
> wine: thunderbird
> region: napa
> country: usa
>
> rather than
>
> wine: thunderbird
> region: 1
> country: 1
>
> Here is a select statement that I am playing with...
>
> sqlText = "SELECT
> featured_wine.wine,featured_wine.regionID,featured_wine.coun tryID FROM
> featured_wine INNER JOIN featured_wine_country ON
> (featured_wine_country.countryID=featured_wine.countryID) AND INNER
> JOIN featured_wine_region ON
> (featured_wine_region.regionID=featured_wine.regionID) WHERE blah,
> blah, blah...
>
> The above did not work. I amable to get a 2 table join to work well,
> but adding the third table throws me off.
>
> This is a where I'd like to turn the 'learning curve' folks.
>
> Thanks
>

No "AND"; it's the parentheses:


sqlText = "SELECT
featured_wine.wine,
featured_wine_region.region,
featured_wine_country.country
FROM (featured_wine
INNER JOIN featured_wine_country
ON featured_wine_country.countryID = featured_wine.countryID)
INNER JOIN featured_wine_region
ON featured_wine_region.regionID = featured_wine.regionID"

Re: 3 table join help needed (I have searched but...)

josephweiss@gmail.com wrote:
> i am still at a loss from after all my time searching for a solution
> to this query (litteraly). Here are my tables

Are you using Access? Please don't make us guess.
> Here is a select statement that I am playing with...
>
> sqlText = "SELECT
> featured_wine.wine,featured_wine.regionID,featured_wine.coun tryID FROM
> featured_wine INNER JOIN featured_wine_country ON
> (featured_wine_country.countryID=featured_wine.countryID) AND INNER
> JOIN featured_wine_region ON
> (featured_wine_region.regionID=featured_wine.regionID) WHERE blah,
> blah, blah...
>
> The above did not work. I amable to get a 2 table join to work well,
> but adding the third table throws me off.

If you are using Access, a good way to learn is by using the Query Builder.
Create a query that does what you want in the Design view GUI, then switch
to SQL View to see what the sql should look like.

Bob Barrows
--
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: 3 table join help needed (I have searched but...)

I am using Access, sorry for the secret. I probably am not using
access as much as I should. I realy just use it to design tables. I
will dig deeper into access and investigate the query builder.
Thanks