?
Solved

SQL Multiple JOINS to same table

Posted on 2008-02-11
2
Medium Priority
?
381 Views
Last Modified: 2010-04-21
I've got this query:

SELECT  View_CMS_Tree_Joined.*, CONTENT_Event.*, act1.*, act2.*
FROM View_CMS_Tree_Joined  
INNER JOIN CONTENT_Event
ON View_CMS_Tree_Joined.DocumentForeignKeyValue = CONTENT_Event.[eventID]
LEFT JOIN custom_activity AS act1
ON CONTENT_Event.[EventActivity] = act1.[activityID]
LEFT JOIN custom_activity AS act2
ON CONTENT_Event.[EventActivity2] = act2.[activityID]

It is returning the correct data - but the field names returned for act1 and act2 are identical - which makes it impossible to distinguish between act1.field1 and act2.field1. I'm obviously doing something wrong here.

Thanks for any help.
0
Comment
Question by:asrisbey
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20869280
avoid using .* ... you will need to put all the field names you need, with each time a distinct alias name

SELECT  View_CMS_Tree_Joined.*
, CONTENT_Event.*
, act1.activityid activityid1, act1.somefield somefield1 .... etc ...
, act2.activityid activityid2, act2.somefield somefield2 .... etc ...
FROM View_CMS_Tree_Joined  
INNER JOIN CONTENT_Event 
ON View_CMS_Tree_Joined.DocumentForeignKeyValue = CONTENT_Event.[eventID]
LEFT JOIN custom_activity AS act1
ON CONTENT_Event.[EventActivity] = act1.[activityID]
LEFT JOIN custom_activity AS act2
ON CONTENT_Event.[EventActivity2] = act2.[activityID]

Open in new window

0
 

Author Closing Comment

by:asrisbey
ID: 31429932
Another great solution from angellll - thanks, works a treat.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question