text: Wine, beer, water, etc ... | dBforums – Everything on Databases, Design, Developers and Administrators
  Wine, beer, water, etc ...
  Thread: Wine, beer, water, etc ...
  10-11-04, 08:20 #1
  Unanswered: Wine, beer, water, etc ...
  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 ?
  10-11-04, 13:27 #2
  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) ?
  10-11-04, 14:01 #3
  Select a drop down from results form another table
  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"
  Thanks in advance for your esteemed guidance
  10-11-04, 14:46 #4
  Sorry, posted twice because the site's DB was down... wait a moment please...
  10-11-04, 15:05 #5
  If I'm right, that should do it :
  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.
  10-11-04, 15:08 #6
  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)
  10-11-04, 18:29 #7
  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?
  10-11-04, 18:52 #8
  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 ?
  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
  on products.Product_Origin_ISO
  = countries.Country_ISO
  10-12-04, 21:09 #9
  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.
  10-13-04, 06:47 #10
  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.