nested select - multi part identifier not bound error

Question

I have written the following query, but I am getting a multi part identifier not bound error, as I am trying to filter the sub query, using values from the first inner join, from outside the sub select.

SELECT runners.id,  wins
FROM dbHorseRacing.dbo.historic_runners as runners
inner join dbHorseRacing.dbo.historic_races as races on races.race_id = runners.race_id 
inner join (
    select ru.runner_id, count(*) as wins
    FROM dbHorseRacing.dbo.historic_runners as ru
    inner join dbHorseRacing.dbo.historic_races as ra on ra.race_id = ru.race_id 
    where ru.runner_id = runners.runner_id
    and ra.meeting_date < races.meeting_date
    and ru.finish_position = 1
    group by ru.runner_id
) w on w.runner_id = runners.runner_id

its the following two line that are causing the problem:

where ru.runner_id = runners.runner_id
        and ra.meeting_date < races.meeting_date

I have tried to write this query in other ways but with no sucess, I have seen other people using nested select, referencing identifiers from outside the nested select...

Is what I am trying to do generally wrong as a principle? If so, is there another way I could achieve it?

I have tried finding an answer and would really appreciate any help!

Laura

You want a kind of running sum. For each runners.id and races, how many times he won previous races? — DanB, Aug 20 '18 at 19:16
when you will use corelated sub-query in that you can use outer query inside of subquery but here you do just with subquery — Zaynul Abadin Tuhin, Aug 20 '18 at 19:19

score 2 · Answer 1 · edited Aug 20 '18 at 19:20

2

Try this :

SELECT runners.id, (
    select count(*)
    FROM dbHorseRacing.dbo.historic_races
    where historic_races.runner_id = runners.runner_id
    and historic_races.meeting_date < races.meeting_date
    and ru.finish_position = 1
) wins
FROM dbHorseRacing.dbo.historic_runners as runners
inner join dbHorseRacing.dbo.historic_races as races on races.race_id = runners.race_id

edited Aug 20 '18 at 19:20

Zaynul Abadin Tuhin

31,407
5
33
63

answered Aug 20 '18 at 19:18

DanB

2,022
1
12
24

Thank you so much Daniel, I have just tried this and it work perfectly! Thanks again, I have lost loads of time over this. – Laura Baker Aug 20 '18 at 19:28

score 0 · Answer 2 · answered Aug 20 '18 at 19:24

below query will work as you not shared your output so i just edited query workable version

SELECT runners.id,  wins
    FROM dbHorseRacing.dbo.historic_runners as runners
    inner join dbHorseRacing.dbo.historic_races as races on races.race_id = runners.race_id 
    inner join (
        select ru.runner_id, count(*) as wins
        FROM dbHorseRacing.dbo.historic_runners as ru
        inner join dbHorseRacing.dbo.historic_races as ra on ra.race_id = ru.race_id 
       where ru.finish_position = 1
        group by ru.runner_id
    ) w on w.runner_id = runners.runner_id

Thank you Zaynul, I have just implemented the version from Daniel, but thank you. :) — Laura Baker, Aug 20 '18 at 19:31

nested select - multi part identifier not bound error

2 Answers2