Improve company productivity with a Business Account.Sign Up

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

Display a list of clubs from a database where a member of a club has more than one role

I want to display a list of clubs in an organisation where the number of roles assigned to a member exceeds 2 and Post A must be included
To get to the data requires joining tables but the information gets displayed like so until I try to introduce the Group by and Count when nothing gets displayed

Name, MemberID, Post, PostID
Club1, 200,  Post A, 1
Club1, 200, Post B, 2
Club2, 300, Post A, 1
Club2, 300, Post C, 3
Club2, 300, Post D, 4

The Result I need should be
Club1
Club2

The query I have is as follows

SELECT Name, MemberID, COP.Post, COP.PostID
FROM ((ClubOfficerPost AS COP LEFT JOIN [table1].clubofficer AS CO on CO.postid = COP.PostID)
LEFT JOIN Member AS M on M.ID = CO.MemberID) LEFT JOIN Club as C on C.ID = CO.ClubID

But when I try to add the Group by and Count using the MemberID nothing gets returned.

SELECT Name, MemberID, COP.Post, COP.PostID
FROM ((ClubOfficerPost AS COP LEFT JOIN [table1].clubofficer AS CO on CO.postid = COP.PostID)
LEFT JOIN Member AS M on M.ID = CO.MemberID) LEFT JOIN Club as C on C.ID = CO.ClubID
Group by MemberID, Name, COP.Post, COP.PostID
HAVING COUNT (MemberID) > 2
0
Milkybar-kid
Asked:
Milkybar-kid
1 Solution
 
Surendra NathTechnology LeadCommented:
try the below code
;WITH CTE AS
(
SELECT Name, MemberID, COP.Post, COP.PostID 
FROM ((ClubOfficerPost AS COP LEFT JOIN [table1].clubofficer AS CO on CO.postid = COP.PostID) 
LEFT JOIN Member AS M on M.ID = CO.MemberID) LEFT JOIN Club as C on C.ID = CO.ClubID 
)
select Name,MemberID 
FROM CTE
GROUP BY Name,MemberID
HAVING COUNT(1) > 1

Open in new window

0
 
PaulCommented:
please try this
SELECT
      C.Name
    , count(CO.MemberID) as office_count 
FROM Club AS C
LEFT JOIN [table1].clubofficer AS CO ON C.ID = CO.ClubID
LEFT JOIN ClubOfficerPost AS COP     ON CO.postid = COP.PostID
LEFT JOIN Member AS M                ON CO.MemberID = M.ID
GROUP BY
      C.Name
HAVING COUNT(CO.MemberID) >= 2

Open in new window

0
 
sriramiyerCommented:
You can try this,

SELECT Name, MemberID, COP.Post, COP.PostID
FROM ((ClubOfficerPost AS COP LEFT JOIN [table1].clubofficer AS CO on CO.postid = COP.PostID)
LEFT JOIN Member AS M on M.ID = CO.MemberID) LEFT JOIN Club as C on C.ID = CO.ClubID
where  COP.Post="POST A"
Group by MemberID
HAVING COUNT (MemberID) > 1
0
 
DOSLoverCommented:
You have a condition that says 'Post A must be included'. And also, if you need only list of club names that satisy all your criteria, here is another way:
Select DISTINCT C.NAME
  from Club C
 where (Select Count(CO.MemberId)
          From ClubOfficer CO 
         where CO.ClubId = C.Id
           and exists 
               (select CO1.PostId from ClubOfficer CO1
                 where CO1.ClubId = CO.ClubId
                   and CO1.memberId = CO.MemberId
                   and CO1.PostId = 1)
       ) >= 2

Open in new window

0
 
awking00Commented:
>>The Result I need should be
Club1
Club2<<
However, you also state where the number of roles (I'm assuming the post column defines a role) exceeds 2, yet your example only shows 2 for Club1.
Can you provide some sample data for the four tables and your expected output?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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