cfoutput query within cfoutput query
I have a situation where i need to display info from two queries on the same row of a table. Here is the background, each person in a database table has a name and ID which is then linked to another table that lists pets. Each person can have as many as 4 pets. Now i need to display the ID, name, and each pet on the same row of table, preferably with the pets all listed in the same cell. I'm not that experienced with ColdFusion so my first attempt looked like this: cfquery datasource=#dsn# name=person SELECT * FROM tblPerson /cfquery table cfoutput query=person tr td#ID#/td td#name#/td cfquery datasource=#dsn# name=pets SELECT * FROM tblPets WHERE ownerID = #person.ID# /cfquery cfoutput query=pets td#pet#br/td /cfoutput /tr /cfoutput /table If you've read this far, i'm sure you know this won't work but i hope can see what i'm trying to do. Please help if have any solutions ~| 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:331908 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfoutput query within cfoutput query
Hi Brandon, 1) Use a cfloop in place of a cfoutput tag for the inner loop. 2) in situations like these I recommend also scoping your variables, eg. the pets in the inner loop would be #pets.pet# 3) in general your approach should be revisited, mainly because it will create and execute a new query for each row returned by query person, consider performing a join operation and reducing your approach to have only one query. Hope that helps, Dan On Fri, Mar 19, 2010 at 4:32 PM, brandon kennedy laxtwenty...@gmail.comwrote: I have a situation where i need to display info from two queries on the same row of a table. Here is the background, each person in a database table has a name and ID which is then linked to another table that lists pets. Each person can have as many as 4 pets. Now i need to display the ID, name, and each pet on the same row of table, preferably with the pets all listed in the same cell. I'm not that experienced with ColdFusion so my first attempt looked like this: cfquery datasource=#dsn# name=person SELECT * FROM tblPerson /cfquery table cfoutput query=person tr td#ID#/td td#name#/td cfquery datasource=#dsn# name=pets SELECT * FROM tblPets WHERE ownerID = #person.ID# /cfquery cfoutput query=pets td#pet#br/td /cfoutput /tr /cfoutput /table If you've read this far, i'm sure you know this won't work but i hope can see what i'm trying to do. Please help if have any solutions ~| 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:331909 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: cfoutput query within cfoutput query
I think my first post failed :-( SQL with JOIN CFOUTPUT with group cfquery datasource=#dsn# name=qryPetPerson SELECT tblPerson.id, tblPerson.name, tblPets.pet FROM tblPerson, tblPets WHERE tblPets.ownerID = tblPerson.id order by tblPerson.id /cfquery table cfoutput query=qryPetPerson group=id tr td#ID#/td td#name#/td cfoutput td#pet#br/td /cfoutput /tr /cfoutput /table On 20 March 2010 06:32, brandon kennedy laxtwenty...@gmail.com wrote: I have a situation where i need to display info from two queries on the same row of a table. Here is the background, each person in a database table has a name and ID which is then linked to another table that lists pets. Each person can have as many as 4 pets. Now i need to display the ID, name, and each pet on the same row of table, preferably with the pets all listed in the same cell. I'm not that experienced with ColdFusion so my first attempt looked like this: cfquery datasource=#dsn# name=person SELECT * FROM tblPerson /cfquery table cfoutput query=person tr td#ID#/td td#name#/td cfquery datasource=#dsn# name=pets SELECT * FROM tblPets WHERE ownerID = #person.ID# /cfquery cfoutput query=pets td#pet#br/td /cfoutput /tr /cfoutput /table If you've read this far, i'm sure you know this won't work but i hope can see what i'm trying to do. Please help if have any solutions ~| 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:331910 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm