Nested Inner Joins with SubSelects

[wooddsw]

I would appreciate any suggestions on the following code and why I'm getting the following error message:
Msg 8156, Level 16, State 1, Line 14
The column 'added_by' was specified multiple times for 'A'.

Code

select *
from sfdc..contact C
inner join
(
select * from avenue2..activity G
inner join (
select e.added_by, COUNT(*) as 'TotalActivities'
from avenue2..activity E
where ACTIVITY_DATE between '2008-01-01'
and '2010-12-31'
group by E.added_by
having COUNT(*) > 0
)
B on G.ADDED_BY = B.ADDED_BY
where G.Activity_Date between '2008-01-01' and '2010-12-31'
)
A on C.retire_stamp2__C = A.Contact_stamp2
where A.date_added between '2010-01-02' and '2010-01-31'

 

[r937]

it's rather straight-forward

the subquery called A looks this --

( SELECT * 
    FROM avenue2..activity G    
  INNER 
    JOIN ( ... ) B 
      ON G.ADDED_BY = B.ADDED_BY
   WHERE ... ) A

with me so far?

okay here you used the dreaded, evil "select star"

this means you get all columns from G as well as all columns from B

both G and B have an added_by column, hence the ambiguity

i think you can work around this problem by assigning an alias to the B column

here's your revised query --

SELECT * 
  FROM sfdc..contact AS c 
INNER 
  JOIN ( SELECT * 
           FROM avenue2..activity AS g    
         INNER 
           JOIN ( SELECT e.added_by [red]AS e_added_by[/red]
                       , COUNT(*) AS TotalActivities
                    FROM avenue2..activity AS e  
                   WHERE activity_date BETWEEN '2008-01-01'  
                                           AND '2010-12-31' 
                  GROUP 
                      BY [red]e_added_by[/red] 
                  HAVING COUNT(*) > 0 ) AS b 
             ON [red]b.e_added_by[/red] = g.added_by
          WHERE g.activity_date BETWEEN '2008-01-01'
                                    AND '2010-12-31' ) AS a 
    ON a.contact_stamp2 = c.retire_stamp2__c
 WHERE a.date_added BETWEEN '2010-01-02' 
                        AND '2010-01-31'

let me know if that fixes it



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon