CODEWITHSUNDEEP
sqlsql-server
David Tunnell
David Tunnell

Reputation: 7542

RETURN value from other table using foreign key retrieved in query in same query

I have built a query:

SELECT [Hours], TaskId FROM DailyTaskHours 
WHERE PointPerson = 'Dave Smith (smithd)' 
AND ActivityDate Between Cast('1/01/13 12:01:01' As DateTime) 
AND Cast('2/01/13 12:01:01' as DateTime)

This returns all of the hours as well as the associated taskId they are billed to. The TaskId is a forign key to the Task table. As part of the same query I want to return a column called Name and a column called StoryId that is on the Task table. How do I accomplish this?

Upvotes: 0

Views: 116

Answers (1)

Lamak
Lamak

Reputation: 70648

This should do:

SELECT  D.[Hours], 
        D.TaskId,
        T.[Name],
        T.StoryId
FROM DailyTaskHours D
INNER JOIN Task T
    ON D.TaskId = T.TaskId
WHERE D.PointPerson = 'Dave Smith (smithd)' 
AND D.ActivityDate Between Cast('1/01/13 12:01:01' As DateTime) 
AND Cast('2/01/13 12:01:01' as DateTime)

But you should always explicitely write the datetime format that you are using (CONVERT(DATETIME,Date,Format), and also the complete year. In your case, is '2/01/13' January 2nd 2013, February 1st 2013, January 2nd 1913?, etc. Also, try to avoid using BETWEEN when querying DATETIME data types (Bad habits to kick : mis-handling date / range queries)

Upvotes: 1

Related Questions