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
240 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
5 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
Comment Utility
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 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
>>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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now