That simple, my bad. I remember having to alias the tables seperately but forgot to alias the field names, cheers.
> -----Original Message----- > From: Everett, Al (NIH/NIGMS) [C] [mailto:[EMAIL PROTECTED] > Sent: 15 August 2006 15:59 > To: CF-Talk > Subject: RE: Multiple joins between the same tables. > > So alias it. > > SELECT > c1.COMPANY_NAME AS BRAND_NAME, c2.COMPANY_NAME AS > LABEL_NAME, c3.COMPANY_NAME AS MANUFACTURER_NAME, > c4.COMPANY_NAME AS PUBLISHER_NAME, c5.COMPANY_NAME AS > STUDIO_NAME > > -----Original Message----- > From: James Smith [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 15, 2006 10:40 AM > To: CF-Talk > Subject: RE: Multiple joins between the same tables. > > > Look good to me. What's wrong? You can use the same table multiple > > times or even joing a table on itself. I personally would > use a LEFT > > JOIN, but it depends what your goal is. > > The problem with using... > > LEFT JOIN t_PI_Companies c1 ON (c1.CompanyID = m.BrandID) > LEFT JOIN t_PI_Companies c2 ON (c2.CompanyID = m.LabelID) > LEFT JOIN t_PI_Companies c3 ON (c3.CompanyID = > m.ManufacturerID) > LEFT JOIN t_PI_Companies c4 ON (c4.CompanyID = > m.PublisherID) > LEFT JOIN t_PI_Companies c5 ON (c5.CompanyID = > m.StudioID) > > Is that the column "CompanyName" in the "t_PI_Companies" > table isn't aliased for each join so whatever the "BrandID" > is from the main table is returned 5 times simply as > "CompanyName". What I need is some way for the "CompanyName" > to be returned 5 times with 5 different names so each can be > accessed separately. > > I could do it by leaving out the companies and simply > querying them separately but I would prefer one query to six > if at all possible. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:249948 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
