SQL Query with subqueries or joins

Question

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

There are the following tables:

tblWorkGroupProgramme

WorkGroupID
Programme ID

tblWorkGroup

WorkGroupID
WorkGroupName

tblUser

UserID
WorkgroupID

tblUserProgramme

UserID
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

Whenever you try something, post what you have tried!

fancyPants
– fancyPants

2012-09-06 10:48:37 +00:00
Commented Sep 6, 2012 at 10:48 — fancyPants
– fancyPants, Commented Sep 6, 2012 at 10:48

Pedigree · Accepted Answer · 2012-09-04 22:57:35Z

1

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

answered Sep 4, 2012 at 22:57

Pedigree

2,6343 gold badges26 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rosebrit3 · Accepted Answer · 2012-09-06 10:07:46Z

0

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%')

answered Sep 6, 2012 at 10:07

rosebrit3

5212 gold badges18 silver badges33 bronze badges

Collectives™ on Stack Overflow

SQL Query with subqueries or joins

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related