Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Simple Join Query

Author  Topic 

nganeshan
Starting Member

1 Post

Posted - 2013-06-14 : 16:38:00
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

52326 Posts

Posted - 2013-06-15 : 13:08:14
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
   

- Advertisement -