CODEWITHSUNDEEP
sqljoinsubquery
rosebrit3
rosebrit3

Reputation: 523

SQL Query with subqueries or joins

Am in need of some guidance on how to write a query.

There are the following tables:

tblWorkGroupProgramme

  1. WorkGroupID
  2. Programme ID

tblWorkGroup

  1. WorkGroupID
  2. WorkGroupName

tblUser

  1. UserID
  2. WorkgroupID

tblUserProgramme

  1. UserID
  2. ProgrammeID

The requirement is to find all those user id whose Workgroup name not like ‘%Insight%’ but ProgrammeID equals 59 as ProgrammeID 59 has to be allotted only to those users who have their Workgroup as ‘%Insight%’

Have been trying with all possible joins and sub queries, but couldnt get it. SO, any help, in the right direction would be of great use

Upvotes: 0

Views: 133

Answers (2)

rosebrit3
rosebrit3

Reputation: 523

This is the answer that i came up with:

select a.UserID, c.WorkGroupName
from dbo.tblUserProgramme a 
where a.ProgrammeID = 59 AND 
a.UserID  IN (SELECT UserID  FROM tblUser a WHERE a.WorkGroupID IN
(SELECT WorkGroupID FROM tblWorkGroup
WHERE WorkGroupName like '%Insight%')

Upvotes: 0

Pedigree
Pedigree

Reputation: 2594

here, try this one.

SELECT      a.UserID
FROM        tblUser a
                INNER JOIN tblWorkGroupProgramme b
                    ON a.WorkgroupID = b.WorkGroupID
                INNER JOIN tblUserProgramme c
                    ON c.ProgrammeID = b.ProgrammeID
                INNER JOIN tblWorkGroup d
                    ON b.WorkGroupID = d.WorkGroupID
WHERE       NOT (d.WorkGroupName LIKE '%Insight%') AND
            c.ProgrammeID = 59

Upvotes: 1

Related Questions