|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now! |
|
#1
July 16th, 2004, 09:07 AM
|
|||
|
|||
|
query confusion
i think it will be easier for me to explain if i show you my table structure first
tblTaskGroups TaskGroupID TaskID GroupID GroupRoleID 1 1 2 2 2 1 3 1 3 2 4 3 4 2 2 2 5 3 1 2 6 3 2 2 7 3 2 1 what i'm having problems with is getting a query to search with a group id from a drop down box and return all the records for a TaskID where GroupID is found in the in the GroupID column. Example: User selects 2 from the drop down box they would see both groupID's for task 1, both groupID's for task 2 and all three groupID's for task 3. all with the item they searched by in bold. does that make sense?
|
|
#2
July 16th, 2004, 09:59 AM
|
|||
|
|||
|
No, it doesn't make sense. Given the example you used, if the user picks Group ID 2 from the list, the task id's that should show up are only ones where the group id is 2. That would be records 1,4,6 and 7. I don't understand how you expect results that have a group id other than 2 to show up.
__________________
Ask if you have a question, but also help answer questions that you have knowledge of! Thanks, Brian. How to Post a Question in the Forums
|
|
#3
July 16th, 2004, 01:25 PM
|
|||
|
|||
|
what i'm doing now is querying over the table and selecting all the taskid's where the group id is present. then i want to query and get all my task info based on the results of the 1st query.
Code:
<cfquery name="Work" datasource="#dsn#"> select DISTINCT TaskID from TaskGroups where #GroupName# = TaskGroups.GroupID </cfquery> <cfset tasklist = ValueList(Work.TaskID)> <cfloop index="i" list="#tasklist#"> <cfquery name="AllTasks" datasource="#dsn#"> SELECT Tasks.taskID, Tasks.taskName, Tasks.taskDesc, SupportCategory.scName as SupportCategory, GroupRole.GroupRole, Groups.groupName FROM Groups RIGHT JOIN ((GroupRole RIGHT JOIN TaskGroups ON GroupRole.GroupRoleID = TaskGroups.GroupRoleID) RIGHT JOIN (SupportCategory RIGHT JOIN Tasks ON SupportCategory.scID = Tasks.taskSupCatID) ON TaskGroups.TaskID = Tasks.taskID) ON Groups.groupID = TaskGroups.GroupID WHERE TaskGroups.TaskID = #i# ORDER BY SupportCategory.scName; </cfquery> </cfloop> <cfdump var="#AllTasks#"> this dump shows that #AllTasks# only holds the information from the last loop. how can i get it to hold all the information it gathers during it's loops? i can do some screen shot if it will help.
|
|
#4
July 18th, 2004, 02:05 PM
|
|||
|
|||
|
Well naturally you're only getting the last query, you're naming each query the same thing so as it loops each new run replaces the previous one.
Regardless, it doesn't look like you need to loop over the second query at all. You can get all the data in one query using IN: Code:
<cfquery name="AllTasks" datasource="#dsn#"> SELECT Tasks.taskID, Tasks.taskName, Tasks.taskDesc, SupportCategory.scName as SupportCategory, GroupRole.GroupRole, Groups.groupName FROM Groups RIGHT JOIN ((GroupRole RIGHT JOIN TaskGroups ON GroupRole.GroupRoleID = TaskGroups.GroupRoleID) RIGHT JOIN (SupportCategory RIGHT JOIN Tasks ON SupportCategory.scID = Tasks.taskSupCatID) ON TaskGroups.TaskID = Tasks.taskID) ON Groups.groupID = TaskGroups.GroupID WHERE TaskGroups.TaskID in (#quotedValueList(Work.TaskID)#) ORDER BY SupportCategory.scName; </cfquery> This could also be done using EXISTS, but that's just for reference.
|
|
| Viewing: Dev Shed Forums > Programming Languages - More > ColdFusion Development > query confusion |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
