#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2002
    Location
    Norwich, UK
    Posts
    84
    Rep Power
    12

    Question Looking for advice on query


    Hi everyone,

    I need to build two drop down menus, with dynamic items, each related to the other via their age. I'm wondering what the best way of doing this is.

    I have two tables, the Name and Category column of the respective tables becoming the dropdown items:

    Table: MACHINE SERIES (columns: MS_ID, NAME, FROM YEAR, TO YEAR)
    Table: AGE (columns: AGE_ID, CATEGORY, FROM YEAR, TO YEAR)

    Basically, the age table's job is to define age categories (for other parts of this application). In this specific task, I need to have to ages of both tables relate to each other.

    For example:

    Machine Series Dropdown
    Machine1, 1972 - 1977
    Machine2, 1996 - 2000
    ...

    Age Dropdown
    Cat1, 1970 - 1980
    Cat2, 1981 - 1990
    ...

    The functionailty I need is that when someone chooses a machine from dropdown 1 and hits submit, the contents of dropdown 2 should be limited to the approriate category and vice versa.

    For example, if I select Machine1 and submit, the age dropdown should only list Cat1. Similarly if I choose Cat1 and submit, the machine series dropdown should only list machines that fall between those years.

    The age categories themselves will change as time moves on, hence I am forced to do an from/to year check.

    Any advice on how to best achieve this would be great! (I am coding in ASP/VBScript btw.)
    Last edited by voice220; December 19th, 2002 at 08:56 AM.
    Yours,
    Frank Reding, IconoclastDesign.co.uk.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2002
    Posts
    693
    Rep Power
    26
    I hope this doesn't come off as rude but what makes this complicated? seems pretty straight forward.
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2002
    Location
    Norwich, UK
    Posts
    84
    Rep Power
    12
    You're right, it probably doesn't qualify as complicated *goes and changes the subject* but I'm still quite new to SQL and my head hurts form post-Christmas party disorder.
    Yours,
    Frank Reding, IconoclastDesign.co.uk.
  6. #4
  7. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    SELECT AGE.AGE_ID, AGE.CATEGORY FROM AGE, MACHINE SERIES WHERE AGE.FROM YEAR = MACHINE SERIES.FROM YEAR AND AGE.TO YEAR = MACHINE SERIES.TO YEAR (use = or the appropriate < >)
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2002
    Location
    Norwich, UK
    Posts
    84
    Rep Power
    12
    Thanks.

    It all started to make a lot more sense today than it did yesterday. Apologies, I really wasn't quite with it. There must have been something in the food.

    Btw, when I tried something along the lines of the above query, I got far too many results, the age categories repeating for every machine series entry. Even adding DISTINCT did not solve it because I drew from two tables.

    I solved by splitting it into two queries, one to find out the relevant to and from data for a given age category, and the second to limit the machine series to those dates. I knwo it's an extra query but it works nicely
    .
    Yours,
    Frank Reding, IconoclastDesign.co.uk.

IMN logo majestic logo threadwatch logo seochat tools logo