SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Simple Join Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nganeshan
Starting Member

1 Posts

Posted - 06/14/2013 :  16:38:00  Show Profile  Reply with Quote
Hi,

I am trying to write a join query for tables below:

Table: Roles

Role Id Role Name
1 Admin
2 User
3 Manager

Table: Department
Dept Id Dept Name
1 Shipping
2 Finance
3 Operations

Table: Workgroup
Row Id UserName Dept Id Role Id
1 ABC 1 1
2 XXX 1 2
3 YYY 2 2

What i want to do is to write a query that joins the above table to display all departments, users and roles associated with the user from the Workgroup table, only where role id is 1 (Admin). So if a department does not have any user with Admin role, then role id should come up as Null.

I hope I explained my problem correctly and any help would be appreciated.

Thanks,
Navin

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/15/2013 :  13:08:14  Show Profile  Reply with Quote
do you mean this?

SELECT *
FROM WorkGroup w
INNER JOIN Department d
ON d.[Dept Id] = w.[Dept Id]
LEFT JOIN Roles r
ON r.[Role Id] = w.[Role Id]
AND r.[Role Name] = 'Admin'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000