asked on

MS SQL join, include all in table 1 and show null when table 2..

MS SQL to include all users and include null when no assigned taskdetail and value when there is a task detail. See below;

Users
User_ID name
1        Joe
2        Mary
3        James
4        Mathew
5        Sam

TaskDetail
TaskDetail_ID FK_User_ID FK_TaskName_ID

1             1            2
2             4            1
3             3            2
4             1            3
5 1 1
6 1 5

I want to have all users returned for a specific task whether they are assigned to it or not.

Result for when FK_TaskName_ID = 2 should result as follows

Name             FK_User_ID        FK_TaskName_ID
Joe             1                  2
Mary             2                  null
James             3                  2
Mathew             4                  null
Sam             5                  null

Jim Horn

Two things:

(1) That would be a LEFT JOIN, which means 'return all rows in the table LEFT of the join, and include only values from the right of the LEFT join where they exist.

(2) >I want to have all users returned for a specific task...
The below CASE block handles that, although I'm sure you'll want to change the hard-coded 2 to include a @variable, or however you're determining the FK_TaskName_ID value.

SELECT users.Name, TaskDetail.FK_User_ID, CASE WHEN TaskDetail.FK_TaskName_ID = 2 THEN 2 ELSE NULL END as FK_TaskName_ID
FROM users
LEFT JOIN TaskDetail ON users.user_id = TaskDetail.FK_USER_ID

petel2k

ASKER

error
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.

'FROM users'

Jim Horn

Check the spelling of all table and column names to make sure they match.
The T-SQL I posted looks correct as far as syntax.

ASKER CERTIFIED SOLUTION

sachitjain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

petel2k

ASKER

Great job!!! Thanks for the help

Jim Horn

How's that answer different from the one I posted earlier? Just curious.
The CASE block does the same as the WHERE.

petel2k

ASKER

Hey Jim..

In your case, your also worked. And I misspelled. Both solutions worked. I need to modify award as a split. Sorry.