drop down menu not pulling results

[quiero mas]

I have populated a dependent two select drop down menu without any trouble
Im not at all sure on the Sql for the next step however.

the drop down menu is Area (group) which populates Language (subgroup)

<cfquery name="get_Main_Group" datasource="dependent">
SELECT distinct group_id, group_name
FROM tAbyL
</cfquery>

<cfquery name="get_Sub_Group" datasource="dependent">
SELECT distinct group_id, subgroup_id, subgroup_name
FROM tAbyL
WHERE group_id = #page.select_Main_Group#
</cfquery>

Database
table 1 (tAbyL)
four fields: group_id, group_name, subgroup_id, subgroup_name

Table 2 (tHbyL)
six fields:hospital_id, hospital_name, group_id, group_name, subgroup_id,
subgroup_name

What i want to do
1. use the populated drop down menu to query Table 2
2. Result is a list of hospital names which are consistent with both the Area
and Language

my attempt
SELECT tHbyL.hospital_name
FROM tAbyL, tHbyL
WHERE (([tAbyL]![subgroup_id]=[tHbyL]![subgroup_id] And [tHbyL]![group_id]));

this sql pulls all names.

result page
<cfparam name="FORM.subgroup_id" default="1">

<cfquery name="Recordset2" datasource="dependent">
SELECT subgroup_id
FROM tAbyL
WHERE subgroup_id = #FORM.subgroup_id#</cfquery>
<cfquery name="Recordset1" datasource="dependent">
SELECT tHbyL.hospital_name
FROM tAbyL, tHbyL
WHERE (([tAbyL]![subgroup_id]=[tHbyL]![subgroup_id] And [tHbyL]![group_id]));
</cfquery>
<cfoutput>#Recordset1.hospital_name#</cfoutput>:confused;

[quiero mas]

sorry forgot to say the problem
1. on the search page as soos as i enter the target for the button the page
when viewed in the web application goes straight to the result page with out
giving me the chance to make the second choice. button is inside the form with
the drop downs.
2. Does the sql seem ok? I think its fine but just want expert opinion

Regards Mark

[Dan Bracuk]

On page1, attempting to run a query based on a selection in a drop down will
always fail. You need a different approach.

On page2, the 1st query (recordset2) doesn't seem to do anythng useful. The
second query (recordset1) shouldn't have a semi-colon. Also, the where clause
appears to be incomplete.

[quiero mas]

???????Dan for your help
May i ask what you mean another approach? I managed to get this working for a
simple sinlge select drop down.
Do you think i should do this another way - without drop downs?
Sorry Dan
this has really been driving me mad.
Once i ve got it done once a whole lot of other doors will open this two
select is really holding me back.
Regards MArk

[Dan Bracuk]

Once a Cold Fusion page loads, nothing else can happen on the server. This
means you don't get to run any more queries.

If you want to query table2 based on a selection in a drop down list, you have
to submit the form.

[quiero mas]

Right thanks a lot ill give it a go. Thanks for you help and advice over the
past few months. this is the last big hump in my project.
Even though ive gone sideways here and there ive learned a lot thanks to this
forum.
When its done ill send you the URL.

Much appreciated Dan
Mark Holloway

[jorgepino]

Hi!
you could use onChange="submit()" if the table you are quering are not too big
but if you want one pull down to change according to the first pull down you will have to use Javascript