select * from crew left join crew_years on crew.id = crew_years.crewid
order by crew.last_name, crew.first_name, crew.id, crew_years.year
then (need to stick much of this on one line to eliminate white space)
<cfoutput group="year">
<cfset FirstYear="True">
#first_name# #last_name#:
<cfoutput>
<cfif FirstYear>
<cfset FirstYear="False">
<cfelse>
,
</cfif>
#Year#
</cfoutput>
<br>
</cfoutput>
----- Original Message -----
From: "Paul Giesenhagen" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Thursday, May 23, 2002 8:19 AM
Subject: OT: Getting single records with multiple records from a join
> I am getting users names from one table and joining the years they have
been with us in another. How would I join the two together and ouput the
years (1995,1996,1997) along with their name.
>
> Here are the tables:
>
> crew
> ===========
> id, first_name, last_name, email
>
>
> crew_years (crewid = id from the crew table)
> ===========
> crewid, year
>
> My outcome would be something like:
>
> John Doe Years: 1999,2002,2003
> Mary Doe Years: 1995,2000,2001
>
> ect..
>
> TIA
>
> Paul Giesenhagen
> QuillDesign
>
>
>
______________________________________________________________________
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
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
