Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
248 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
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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

885 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