Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

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
0
petel2k
Asked:
petel2k
  • 3
  • 3
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
0
 
petel2kAuthor Commented:
error
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.

'FROM users'
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
sachitjainCommented:
select U.Name, T.FK_User_Id, T.FK_TaskName_ID
from Users U left join TaskDetail T on U.UserId = T.FK_User_Id and T.FK_TaskName_ID = 2
0
 
petel2kAuthor Commented:
Great job!!! Thanks for the help
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
How's that answer different from the one I posted earlier?  Just curious.
The CASE block does the same as the WHERE.
0
 
petel2kAuthor Commented:
Hey Jim..

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now