|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
|
Stay one step ahead of the competition. Evaluate and give feedback
on some of the hottest web development tools on the market today.
Make your opinion heard! Click
Here
|
|
#1
|
||||
|
||||
|
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.)
__________________
Yours, Frank Reding, IconoclastDesign.co.uk. Last edited by voice220 : December 19th, 2002 at 08:56 AM. |
|
#2
|
|||
|
|||
|
I hope this doesn't come off as rude but what makes this complicated? seems pretty straight forward.
|
|
#3
|
||||
|
||||
|
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.
|
|
#4
|
||||
|
||||
|
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 < >)
__________________
My blog about OpenSource Databases PDF tutorials about OSS databases, DBMonster ... Please contribute to Open Source Development, fill bug reports!!! Developer Shed eSupport Commented my.ini/my.cnf (PLEASE ADD YOUR OWN CONFIG TRICK) An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries Don't turn your outer joins into inner joins |
|
#5
|
||||
|
||||
|
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 . |
![]() |
| Viewing: Dev Shed Forums > Databases > Database Management > Looking for advice on complicated query |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|