SQL query help
I currently have the following structure:
table: lodges
LodgeID (PK)
Lodge
LodgeDetails
etc
table: nominations
NominationID (PK)
Nomination
Category
Year
LodgeID
etc
table: scores
ScoreID (PK)
Score
ScoreYear
LodgeID
etc
table: trade_users
UserID (PK)
LodgeID
User
etc
And I have a page which lists any lodges which have either a 2015 Nomination or a 2015 Score (i.e. nominations.Year = 2015 OR scores.Year = 2015) for the currently logged in user using the following SQL:
SELECT DISTINCT lodges.LodgeID, Lodge, banner_image_new, Country, banner_image_new_title, photo_one, photo_one_title, Summary_2013, 2014_Nominee, GSG2014, Year, ScoreYear, Nominee, Finalist, HighlyCommended, RunnerUp, Winner, Category, 2015_Paid, 2015_Awards_Participant FROM lodges LEFT JOIN nominations ON lodges.LodgeID = nominations.LodgeID LEFT JOIN scores ON lodges.LodgeID = scores.LodgeID WHERE lodges.UserID = %s AND (Year = 2015 OR ScoreYear = 2015) ORDER BY Lodge ASC
Which works, but lists results:
Lodge A - Category 1
Lodge A Details
Lodge A - Category 2
Lodge A Details
Lodge A - Category 3
Lodge A Details
Lodge B - Category 1
Lodge B Details
etc
How could I change that to list the results in this way:
Lodge A
Category1, Category2, Category3
Lodge A Details
Lodge B
Category 2, Category 5
Lodge B Details
etc