Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
247 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 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

670 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