Solved

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

Posted on 2013-06-21
5
246 Views
Last Modified: 2013-06-26
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
Comment
Question by:Milkybar-kid
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39267395
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39267401
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
 
LVL 5

Expert Comment

by:sriramiyer
ID: 39267475
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
 
LVL 5

Accepted Solution

by:
DOSLover earned 500 total points
ID: 39267503
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
 
LVL 32

Expert Comment

by:awking00
ID: 39268313
>>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

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question