SELECT
c.id,
c.first_name,
c.last_name,
c.email,
cy.year
FROM crew c, crew_years cy
WHERE c.id = cy.crewid
<cfoutput query="thequery" group="id">
#first_name# #last_name#: <cfoutput>year,</cfoutput>
</cfoutput>
You'll want to use the outer join syntax that was provided the other day if
you want to grab people without any years associated with them.
Adam.
> -----Original Message-----
> From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, May 22, 2002 3:19 PM
> To: CF-Talk
> 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
>
>
>
______________________________________________________________________
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.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
