Help with Query and Loop
I have a table that contains user and station information, as well as a boolean field for whether the information is displayed or not. It looks roughly like this: uStationID userID_fk stationID_fk checked I have another very similar table that looks like this cStationID customerID_fk stationID_fk checked On my pages I have two select boxes. The first select box is populated by a query that gets all the values by the userID_fk field whose value is passed when the user is logged in and which have the value of checked. Somehow, I need to populate the second select with values from the second table that returns only those records from the second table that match the stationID_fk and checked of the first query, as well as are constrained by the checked field in the second query. As an illustration, say that users table has the following entries: userID_fk [1] stationID_fk [1] checked [0] userID_fk [1] stationID_fk [2] checked [1] userID_fk [1] stationID_fk [3] checked [0] userID_fk [1] stationID_fk [4] checked [0] So here, the query will return only the value of the second and forth row. Now, I need to get the rows from the customers table that match the value of these row (and any other rows that are returned). customerID_fk [1] stationID_fk [1] checked [0] customerID_fk [1] stationID_fk [2] checked [1] customerID_fk [1] stationID_fk [3] checked [1] customerID_fk [1] stationID_fk [4] checked [0] Here, the 2nd and 3rd rows are both checked, but I only want the second row to return since the first query contains a matching value. Does anyone have any idea how I could get this to work, or is there a better way to set up my database to accomplish this? I started off with lists for each, but that wasn't working either. Thanks in advance for the help. ~| Create Web Applications With ColdFusion MX7 Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:283688 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Help with Query and Loop
As an illustration, say that users table has the following entries: userID_fk [1] stationID_fk [4] checked [0] So here, the query will return only the value of the second and forth row. The forth row isn't checked. I'm assuming that's just a typo. If I'm understanding correctly you need to do a JOIN on the station id and checked fields. Something like this SELECT t2.cStationID, t2.customerID_fk, t2.stationID_fk, t2.checked FROMfirstTable t1 INNER JOIN secondTable t2 ON t1.stationID_fk = t2.stationID_fk AND t1.checked = t2.checked WHERE t1.userID_fk = 123 --- or whatever the userid is AND t1.checked = 1 ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:283691 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Help with Query and Loop
As an illustration, say that users table has the following entries: userID_fk [1] stationID_fk [4] checked [0] So here, the query will return only the value of the second and forth row. The forth row isn't checked. I'm assuming that's just a typo. If I'm understanding correctly you need to do a JOIN on the station id and checked fields. Something like this SELECTt2.cStationID, t2.customerID_fk, t2.stationID_fk, t2.checked FROM firstTable t1 INNER JOIN secondTable t2 ON t1.stationID_fk = t2.stationID_fk AND t1.checked = t2.checked WHERE t1.userID_fk = 123 --- or whatever the userid is AND t1.checked = 1 That was awesome---you totally nailed it. Thanks... you seriously saved my life. ~| Macromedia ColdFusion MX7 Upgrade to MX7 experience time-saving features, more productivity. http://www.adobe.com/products/coldfusion?sdid=RVJW Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:283692 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Help with Query and Loop
No magic involved. But glad you'll be alive to enjoy the weekend ;-) ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJP Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:283694 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
