mHelpMe mHelpMe - 5 months ago 9
SQL Question

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

I have two tables both shown below.

tblId:

Id Name
1A James
23 Holly
33 Rob


tblData:

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

Have you considered a join?

with tick as (
    select Name from tblId where Id = 33
   )
select t.Date, t.Score
from tblData t join
     tick
     on t.name = tick.name
where t.Date >= '2016-06-01'
order by t.Date
Comments