Looking for advice on complicated 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.)

I hope this doesn't come off as rude but what makes this complicated? seems pretty straight forward.

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.

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 < >)

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
.