Wine, beer, water, etc ...

JohnStrecker · #1 (**permalink**) 10-11-04, 07:20

Hi!

I have a 'products' table containing wines, beers, spirits, sodas and mineral waters in which there is a Product_Country_ISO field defining the country the product comes from.

I have another 'countries' table with a Country_ISO field and a Country_name field.

When a client selects a product type (say 'Wine') I want a drop down box displaying the 'names' of the countries where the wines come from (which are not the same as the countries where the beers come from or the mineral waters).

Is there and efficient / elegant solution to accomplish that ?

Thanks for your inspiration

RBARAER · #2 (**permalink**) 10-11-04, 12:27

Hello,

I want to be sure I understand well what you want to do : does the client choose a category of drink or a drink in particular ? Are there drink categories in your db or am I misunderstanding ? Could you please be more precise about the structure of your tables (exact fields and constraints) ?

Regards,

RBARAER

JohnStrecker · #3 (**permalink**) 10-11-04, 13:01

Hello RBABAER

I thought I might see you show up on this one

My "products" table contains wine, beer, etc. Each record has a Product_type and a Product_Origin_ISO field.

When the client selects type = 'Beer' I want the "Countries of origin" drop down box to only display the beer producing countries so he can choose between belgian beers, or danish beers or French beers etc.

When he selects type = 'Wine' I want the countries drop down box to only display the wine producing countries so he can choose between french wines, italian wines, etc ... and not see "Denmark" show up ...

For a more actual experience of the problem please have a look at www.justanybeer.com - "Order here" tag - : when you select beer OR wine you will see the same countries showing up in the "Countries of origin" drop down box.

In fact we are talkingg about "How to define the fields in a drop down box originating from a table being defined by the select from another table"

Looking forward to hear from you

Thanks in advance for your esteemed guidance

Best regards

John in Belgium

RBARAER · #4 (**permalink**) 10-11-04, 13:46

Sorry, posted twice because the site's DB was down... wait a moment please...

RBARAER · #5 (**permalink**) 10-11-04, 14:05

Ok,

If I'm right, that should do it :

Code:

Select country_name
From products, countries
Where products.Product_type = [selected type]
And products.Product_Origin_ISO = countries.Country_ISO;

It is a JOIN on the countries' id. Just populate the dropdown box with the resultset.

However, I suppose each product has only ONE country, and you seem to mean that there could be many... I'm not sure I understand your schema well. Could you please give me the exact definition of your tables ? Do you have another table between products and countries ? If you want a product to come from many countries, you will need such a table.

Regards,

RBARAER

JohnStrecker · #6 (**permalink**) 10-11-04, 14:08

in short I suppose I could do a

SELECT Product_Origin_ISO from products
WHERE product_type = (the type of product chosen by the client)
GROUP BY Product_Origin_ISO

My question then is : how do I

SELECT Country_Name from countries
WHERE Country_ISO = ... or ... or ... or ... (all the Product_country_ISO rows generated by the first query)

Does this make it any clearer ?

Thanks again

JohnStrecker · #7 (**permalink**) 10-11-04, 17:29

It is not working, largely I suspect because I have not been accurate in the tables structure description. Worked until 3am yesterday, although it is "only" 23:30 it's time to go to bed. I'll come back tomorrow with reliable information.

Though ... I was under the impression that in order to join two tables those two tables needed to have an identical column name ... no ? Or am I wrong?

Regards

Jean

r937 · #8 (**permalink**) 10-11-04, 17:52

Quote:

Originally Posted by JohnStrecker

I was under the impression that in order to join two tables those two tables needed to have an identical column name ... no ?

no, absolutely not

you can join tables on any columns you wish (within the constraints that you may face data type mismatch problems joining numeric to character columns, etc., and of course if you join on columns where the data doesn't match, you won't get any results)

use RBARAER's query but add DISTINCT because you don't want the country for every product in the type, you want only the unique countries

Code:

select distinct Country_name
  from products
inner
  join countries
    on products.Product_Origin_ISO 
     = countries.Country_ISO
 where products.Product_type = [selected type]

JohnStrecker · #9 (**permalink**) 10-12-04, 20:09

Well I don't know how to say this but THANK YOU !!!!! to both of you. You have helped me A LOT and saved me countless hours of trial an error.

If you want to see the results goto www.justanybeer.com and click on "Order here"

Web site won't be "Go!" until end of this week though

.

Again many thanks. Although I have done database programmation since 1985 with Ashton Tate's dBase 3 and 4 I am a novice in SQL and forums are a totally new thing to me and I find it inspiring that people like you two take the time and the effort to help us all out.

Have a wonderful week !

Best from Belgium

John

RBARAER · #10 (**permalink**) 10-13-04, 05:47

You're welcome, John !

I'm quite new to db forums too, but from the ones I tried, this one is really THE BEST.
I hereby take time to really thank people like Tony Andrews and "r937" who really are GREAT DB people, always eager to help, and do it the right way. I could also mention "The_Duck", "beilstw", "shoblock", and I certainly forget many others. In another style, in my opinion a little too "RTFM"-oriented

, "anacedent" undoubtedly contributes to the good of this forum too.

Anyway, to everybody contributing to dbforums, THANK YOU !

Best Regards,

RBARAER

Internet Services

Web Development

Digital Marketing

Consumer Tech