Hello. I'm hoping someone here can help me. I have a search form that is submitted (with a number of criteria but for this purpose) with a proposal number (Pipe_id) and what type of file the user is looking for (proposal (chkProposal), solicitation (chkSolicitation), debrief (chkDebrief)). They have to select one file_type to get to my query (otherwise it uses another query). In the results they want to see the proposal number (*one time) and all associated documentation (field_type). There will be a number of other joins but that will follow after I work this out.
Sample of records in the table of files: (You'll see pipe_id can be duplicated here but it is a unique value in its parent table) table: vSPDR_docs Pipe_id File_id File_title ===================================== 98991 0 proposalrevision.doc 98991 0 tableofcontents.doc 98991 1 visio.vsd 98991 2 08fmla.doc 92874 0 allergies.ppt 87171 1 statementofwork.doc 45663 2 debrief0999.pdf File_id key: 0 is proposal, 1 is solicitation, 2 is debriefing Desired Output Example (if they selected all 3 file_types or proposals or solicitations) and btw 2 is a bit of a wildcard: Records MUST have 0 AND 1 file_ids (and can have 2 - but doesn't have to) ============== 98991 Records with 0 file_id (and possibly 2) ============== 92874 Records with 1 file_id (and possibly 2) ============== 87171 Records with just 2 ============== 45663 Desired Output (if they just selected 2 - debriefings): Records with just 2 ============== 45663 I hope this makes sense to you. I appreciate ANY help. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:326244 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4