mHelpMe mHelpMe - 1 year ago 65
SQL Question

Use returned value from query in the a where clause for another select query

I have two tables both shown below.


Id Name
1A James
23 Holly
33 Rob


Date Score Name
2016-06-01 3.5 James
2016-06-01 4.5 Holly
2016-06-01 5.5 Rob
2016-06-01 2.5 James
2016-06-01 3.5 Holly
2016-06-01 6.5 Rob
2016-06-01 7.5 James
2016-06-01 11.5 Holly
2016-06-01 1.5 Rob

I have written the query below but it doesn't like the tick.Name bit saying multi-part identifier could not be bound.

I know that only one record will ever be returned by the

select Name
from tblId
where Id = 33

query. What is the best way to use this value in the where part of my next select statement?

;with tick as
select Name from tblId where Id = 33
select Date, Score
from tblData
where Name = tick.Name and Date >= '2016-06-01'
order by Date

Answer Source

Have you considered a join?

with tick as (
    select Name from tblId where Id = 33
select t.Date, t.Score
from tblData t join
     on =
where t.Date >= '2016-06-01'
order by t.Date
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download