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
