Actually, there is a lot more data in table one than I showed in my example. The reason for the output is a Spreadsheet type layout for printable reports. We want to be able to output the data from the join tables in one "cell". I know this is very easy to do in CF using queries within the output, however, we have a created a simple/dynamic way to add columns/data to these reports, and would love to take care of this "join" in SQL Server to preserve the simplicity / performance of the app. It's not of the utmost importance, but figured I'd see if anyone has seen/done this before I threw the idea out the window.
- j -----Original Message----- From: Matthew R. Small [mailto:[EMAIL PROTECTED]] Sent: Friday, March 22, 2002 9:33 AM To: CF-Talk Subject: RE: OT SQL Join I'm not sure that you need a join at all. It looks like you've already got the information you want in table 2. It might just be a question of sorting through it properly. <cfquery name="queryname" datasource="dsn"> Select * from table2 order by industry_id </cfquery> Then <table> <tr><td>Contact ID</td><td>industries</td></tr> <cfoutput query="queryname" group="industry_id"> <tr><td>#industry_id#</td><td> <cfoutput group="contact_id"> #contact_id# </cfoutput> </td></tr> </cfoutput> </table> This will give you the output you're looking for. I'm not sure why you need the first table. Maybe you could explain it to us a little more if this is not right. - Matt Small -----Original Message----- From: Jim Curran [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 21, 2002 7:37 PM To: CF-Talk Subject: OT SQL Join Hey all, Anyone know how to do the following SQL join (if it's even possible) Table 1 Contact_ID Name 1 Joe 2 John 3 Jerry Table 2 (JN table Using letters here for clarification) industry_ID Contact_ID 1 A 1 B 1 C 2 A 2 B 3 A Results I want: contact_ID industries 1 A,B,C 2 A,B 3 A I know how to do in CF, but would love to figure out if it's possible/efficient in SQL TIA - j -----Original Message----- From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 21, 2002 5:14 PM To: CF-Talk Subject: Re: multiple aggregrates in single query Cantrell, Adam wrote: > Anyone know how to get this in SQLserver without two queries? I'm outputting > something like: "10 incomplete out of 23 for user Joe Shmoe". I know this is > totally wrong, but maybe someone will get my drift: > > <CFQUERY> > SELECT > tIncomplete.count(*) AS incompleteTasks, > tTotal.count(*) AS totalTasks > FROM TASKS AS tIncomplete, TASKS AS tTotal > WHERE > tIncomplete.num_peopleID = 184 AND > tIncomplete.bit_complete = 0 AND > tTotal.num_peopleID = 184 > </CFQUERY> Datatypes? What is the definition of an incomplete task? What is the definition of a completed task? Jochem ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
