You could do a query of queries. You dynamically create a query result set that has your aDate values (cfloop over the range, and add a new row and value on each iteration). Run a query of your actual db table limited to the dates in question. Then run a query of queries where you do a join (I never remember my terms...left outer?) where it's all rows from the generated aDate query set and matching values from aTable. I can't remember if you can do outer joins in queries of queries, but I think this would work.
HTH, Matthieu -----Original Message----- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Thursday, November 03, 2005 10:20 AM To: CF-Talk Subject: SQL join a table to a set of values not in a table. Say one has a table something like this (much simpler then the table I am working with, but should suffice for an example). aTABLE DATEcol VALUEcol ------- -------- 9/1/05 Red 9/3/05 Blue 9/5/05 Green I would like to create a result set that looks like this. aResult aDATE DATEcol VALUEcol ------ ------- -------- 9/1/05 9/1/05 Red 9/2/05 NULL NULL 9/3/05 9/3/05 Blue 9/4/05 NULL NULL 9/5/05 9/5/05 Green I imagine that I would create some kind of inline select or from table, but I can't visulize how I would do this. Is it possible? The date range will be dynamic so I don't really want to create some table that has all possible dates for all of time, it would be a bit large I believe. -------------- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA "C code. C code run. Run code run. Please!" - Cynthia Dunning Confidentiality Notice: This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223056 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
