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:249846
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
