CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
john
john

Reputation: 4158

SQL query returns multiple rows when trying to find specific value

I have 2 tables. One is called "Tasks" and the other one is called "TaskDescription" in my "Task" the setup looks like this: "taskID(primary)","FileID","TaskTypeID" and a bunch of other columns irrelevant.

Then in my "TaskDescription", the setup looks like: "TaskTypeID", "TaskTypeDesc"

so for example if TaskTypeID is 1 , then the description would be"admin" or if TaskTypeID is 2, then TaskTypeDesc would be "Employee" etc.

The two tables have a relationship on the primary/foreign key "TaskTypeID".

What I am trying to do is get a task id, and the TaskDesc where the FileID matches the @fileID(which I pass in as a param). However in my query I get multiple rows returned instead of a single row when trying to obtain the description.

this is my query:

SELECT taskid,
       ( 'Task ID: '
         + Cast(cf.taskid AS NVARCHAR(15)) + ' - '
         + Cast((SELECT DISTINCT td.tasktypedesc FROM casefiletaskdescriptions
         td JOIN
         casefiletasks cft ON td.tasktypeid=cft.tasktypeid WHERE cft.taskid =
         1841 )AS
         NVARCHAR(100))
         + ' - Investigator : ' + ( Cast(i.fname AS NVARCHAR(20)) + ' '
                                    + Cast(i.lname AS NVARCHAR(20)) ) ) AS
       'Display'
FROM   casefiletasks [cf]
       JOIN investigators i
         ON CF.taskasgnto = i.investigatorid
WHERE  cf.fileid = 2011630988
       AND cf.concluded = 0
       AND cf.progressflag != 'Conclude'

I am trying to get the output to look like "Task ID: 1234 - Admin - Investigator : John Doe". However I am having trouble on this part:

CAST((select DISTINCT td.TaskTypeDesc from CaseFileTaskDescriptions td 
JOIN CaseFileTasks cft ON td.TaskTypeID=cft.TaskTypeID 
where cft.TaskID =1841 )as nvarchar(100))

This seems to work but the problem is I have to hard code the value "1841" to make it work. Is there a way to assign a "taskID" variable with the values being returned from the TaskID select query, or will it not work since I think sql runs everything at once instead of line by line.

EDIT-this is in Microsoft SQL Server Management Studio 2008

Upvotes: 0

Views: 2080

Answers (3)

Kermit
Kermit

Reputation: 34053

You can dynamically reference a column that exists in your FROM set. In this case, it would be any column from casefiletasks or investigators. You would replace 1841 with the table.column reference.

Update

Replacing your static integer with the column reference, your query would look like:

SELECT taskid,
       ( 'Task ID: '
         + Cast(cf.taskid AS NVARCHAR(15)) + ' - '
         + Cast((SELECT DISTINCT td.tasktypedesc FROM casefiletaskdescriptions
         td JOIN
         casefiletasks cft ON td.tasktypeid=cft.tasktypeid WHERE cft.taskid =
         cf.taskid )AS
         NVARCHAR(100))
         + ' - Investigator : ' + ( Cast(i.fname AS NVARCHAR(20)) + ' '
                                    + Cast(i.lname AS NVARCHAR(20)) ) ) AS
       'Display'
FROM   casefiletasks [cf]
       JOIN investigators i
         ON CF.taskasgnto = i.investigatorid
WHERE  cf.fileid = 2011630988
       AND cf.concluded = 0
       AND cf.progressflag != 'Conclude'

Upvotes: 2

Becuzz
Becuzz

Reputation: 6864

Why not just do another join instead of a subquery?

SELECT taskid,
       ( 'Task ID: '
         + Cast(cf.taskid AS NVARCHAR(15)) + ' - '
         + Cast(td.tasktypedesc AS NVARCHAR(100))
         + ' - Investigator : ' + ( Cast(i.fname AS NVARCHAR(20)) + ' '
                                    + Cast(i.lname AS NVARCHAR(20)) ) ) AS
       'Display'
FROM   casefiletasks [cf]
       JOIN investigators i
         ON CF.taskasgnto = i.investigatorid
       JOIN casefiletaskdescriptions td
         ON td.tasktypeid = cf.tasktypeid
WHERE  cf.fileid = 2011630988
       AND cf.concluded = 0
       AND cf.progressflag != 'Conclude'

Upvotes: 1

ae14
ae14

Reputation: 411

Would this work as your inner query?

SELECT DISTINCT td.TaskTypeDesc FROM CaseFileTaskDescriptions td 
JOIN CaseFileTasks cft ON td.TaskTypeID = cft.TaskTypeID 
WHERE cft.TaskID = cf.TaskID

Upvotes: 1

Related Questions