Query not quite specific enough

[KitCarl]

I'm at a loss as to how to change this query to generate the proper data. I need to generate the member names and offices they hold for a specific club. This query gives me the correct list of names for the specific club, but lists offices they hold in all clubs. How do I select only those offices held for that specific club. I hope I supplied enough info but if not please tell me what to add.

 

SELECT CONCAT_WS(' ', t1.firstName, LEFT(t1.middlename, 1), t1.nickName, t1.lastname, t1.suffix) AS name,  Group_Concat(t5.officeName ORDER BY t5.officeID SEPARATOR '<br />') AS office
FROM People t1 
Left Join PeopleClub t3 ON t1.peopleID=t3.peopleID
Left Join ClubOfficers AS CO ON t3.peopleID=CO.peopleID
Left Join Office t5 ON t5.officeID=CO.officeID
Where t3.clubID = 12
Group BY name
Order by t1.lastName, t1.firstName

 

ClubOfficers Table

 

clubOfficerID INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  peopleID INT(6) UNSIGNED NOT NULL,
  clubID INT(6) UNSIGNED NOT NULL,
  officeID INT(11) UNSIGNED DEFAULT NULL,
  startDate DATE DEFAULT NULL,
  leaveDate DATE DEFAULT NULL,
  PRIMARY KEY (clubOfficerID)

 

Office Table

 

officeID INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  officeName VARCHAR(255) NOT NULL,
  PRIMARY KEY (officeID)

[kickstart]

Hi

 

Think you need to join PeopleClub and ClubOfficers on the ClubId as well as the PeopleId

 

All the best

 

Keith

[KitCarl]

Kickstart, I tried adding the line "LEFT Join ClubOfficers AS t4 ON t4.clubID = t3.clubID" but that just adds every possible office to each name. I Probably didn't add it in the way that you meant?

 

SELECT CONCAT_WS(' ', t1.firstName, LEFT(t1.middlename, 1), t1.nickName, t1.lastname, t1.suffix) AS name,  Group_Concat(t5.officeName ORDER BY t5.officeID SEPARATOR '<br />') AS office
FROM People t1
Left Join PeopleClub t3 ON t1.peopleID=t3.peopleID
Left Join ClubOfficers AS CO ON t3.peopleID=CO.peopleID
LEFT Join ClubOfficers AS t4 ON t4.clubID = t3.clubID
Left Join Office t5 ON t5.officeID=CO.officeID
Where t3.clubID = 12
Group BY name
Order by t1.lastName, t1.firstName

 

 

 

 

This one returns the Id and proper offices for only members who are officers of the club

 

SELECT t1.peopleID, CONCAT_WS(' ', t1.firstName, LEFT(t1.middlename, 1), t1.nickName, t1.lastname, t1.suffix) AS name,
  Group_Concat(t5.officeName ORDER BY t5.officeID SEPARATOR '<br />') AS office
FROM People t1 
Left Join PeopleClub t3 ON t1.peopleID=t3.peopleID
Left Join ClubOfficers AS CO ON t3.peopleID=CO.peopleID
Left Join Office t5 ON t5.officeID=CO.officeID
Where t3.clubID = 12
  AND CO.clubID = 12
Group BY name
Order by t1.lastName, t1.firstName

 

This one returns All members of the club

 

SELECT t1.peopleID, CONCAT_WS(' ', t1.firstName, LEFT(t1.middlename, 1), t1.nickName, t1.lastname, t1.suffix) AS name 
FROM People t1
Left Join PeopleClub t3 ON t1.peopleID=t3.peopleID
Where t3.clubID = 12

 

Is there a way to join the two queries as one table on the t1.peopleID field?

Or is there just a completely better way to write the query?

[kickstart]

Hi

 

Sorry, meant JOIN on a match on both fields, not do 2 joins one on each field

 

SELECT CONCAT_WS(' ', t1.firstName, LEFT(t1.middlename, 1), t1.nickName, t1.lastname, t1.suffix) AS name,  Group_Concat(t5.officeName ORDER BY t5.officeID SEPARATOR '<br />') AS office
FROM People t1
Left Join PeopleClub t3 ON t1.peopleID=t3.peopleID
Left Join ClubOfficers AS CO ON t3.peopleID=CO.peopleID AND t3.clubID = CO.clubID
Left Join Office t5 ON t5.officeID=CO.officeID
Where t3.clubID = 12
Group BY name
Order by t1.lastName, t1.firstName 

 

All the best

 

Keith

[KitCarl]

Kickstart, Thank you so very much 

 

I had spent hours on this one to no avail.