Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2004
    Posts
    39

    Unanswered: Wine, beer, water, etc ...

    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

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    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

  3. #3
    Join Date
    Sep 2004
    Posts
    39

    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"

    Looking forward to hear from you

    Thanks in advance for your esteemed guidance

    Best regards

    John in Belgium

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Sorry, posted twice because the site's DB was down... wait a moment please...

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    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

  6. #6
    Join Date
    Sep 2004
    Posts
    39

    / continued

    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

  7. #7
    Join Date
    Sep 2004
    Posts
    39
    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

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2004
    Posts
    39
    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

  10. #10
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •