Re: Flatten out a relational database
May I be so pertinant as to ask why it must be so? i.e. it may be more efficient and easier to read to do that bit in ColdFusion. So get the query to return like: permit info,company1 permit info,company2 Then, if you were outputting to excel for example, do: tr cfoutput query=thequery groupby=permitId td#permitName#/td cfoutput td#companyname#/td /cfoutput /cfoutput /tr I do realise this may not be what you are after, but I would most likely be inclined to let coldfusion deal with that kind of stuff if possible. Dominic -- Blog it up: http://fusion.dominicwatson.co.uk ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297282 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Flatten out a relational database
I don't just want: permit info,company1 permit info,company2 Why not? If you use the GROUP attribute in CFOUTPUT, you can make your query really look like Permit Info 1-Company1 2-Company2 -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297317 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Flatten out a relational database
I need a query that will flattion out a relational database. I have 2 objects. Permits and Companies. Right now you enter a permit, then you can add 1-n companies and attach them to that permit. A query would return Permit Info 1-Company1 2-Company2 etc... I want to return Permit Info,Company1,Company2 This smacks of a crosstab or pivot query, but I am not sure how to make it work, or if either of those are a good solution. I don't just want: permit info,company1 permit info,company2 That is just an easy join. TIA, -- David Mineer Jr - The critical ingredient is getting off your butt and doing something. It's as simple as that. A lot of people have ideas, but there are few who decide to do something about them now. Not tomorrow. Not next week. But today. The true entrepreneur is a doer. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297238 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Flatten out a relational database
It's not a simple join that you're looking for. You'll need to make use of an index and temporary table. What is the database engine you're using for this? Hatton On Jan 23, 2008 7:35 PM, David Mineer [EMAIL PROTECTED] wrote: I need a query that will flattion out a relational database. I have 2 objects. Permits and Companies. Right now you enter a permit, then you can add 1-n companies and attach them to that permit. A query would return Permit Info 1-Company1 2-Company2 etc... I want to return Permit Info,Company1,Company2 This smacks of a crosstab or pivot query, but I am not sure how to make it work, or if either of those are a good solution. I don't just want: permit info,company1 permit info,company2 That is just an easy join. TIA, -- David Mineer Jr - The critical ingredient is getting off your butt and doing something. It's as simple as that. A lot of people have ideas, but there are few who decide to do something about them now. Not tomorrow. Not next week. But today. The true entrepreneur is a doer. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:297259 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
