2

Possible duplicate, but providing no insight to this case: The multi-part identifier could not be bound

I have a query of the following form:

select l.id, l.foo, r.id, r.foo
from tbl l 
    inner join storyevents r on l.id = r.id
    right join (
        select distinct foo from tbl where id= l.id
    ) tmp on l.foo = tmp.foo
where l.foo = 12345

But i get the following error:

The multi-part identifier "l.id" could not be bound.

in relation to the right join sub query.

Bonus points: This is an attempt to remove duplicate rows from the inner join based on a single column. Better way to do this?

Community
  • 1
  • 1
Kyle
  • 1,366
  • 2
  • 16
  • 28

2 Answers2

1

try this,

SELECT l.id, l.foo, r.id, r.foo
FROM   storyevents l 
         INNER JOIN storyevents r 
               ON l.id = r.id
         RIGHT JOIN 
         (
               SELECT distinct extid, foo 
               FROM storyevents
         ) tmp on l.foo = tmp.foo AND
                  tmp.extid = l.id
where l.foo = 12345
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Answer as this resolved the error (scope of the ID reference). Still does not cut down result set to match those in tmp though. – Kyle Aug 23 '12 at 02:59
0

Better way to remove duplicate rows?

 select distinct l.id, l.foo, r.id, r.foo 
 ....
podiluska
  • 50,950
  • 7
  • 98
  • 104
  • Unfortunately the combination of id and foo on l and r results in 'unique' combinations of the same rows multiple times. If I could apply distinct to just foo this would be perfect – Kyle Aug 23 '12 at 00:26