Not sure if this is possible, but I’ll try my best to explain…
I have a couple of tables, Lodges and Scores - one to many where any lodge can have multiple scores.
From this, I have a list of lodges using GROUP BY lodge, showing any lodge that has any scores, and the average score for that lodge.
This all works great - however, there is also a Comment field in the Scores table, and in this list I would like to include a link to the comments if the lodge has any records in the Scores table with a comment.
I have it working, but would like the link not to display if the lodge has not received any comments. Otherwise people see the link, click on it, and there might not be any comments.
So the problem, I assume, is because using GROUP BY, it may or may not happen to show a record from the scores table that includes a comment.
I have tried using ORDER BY as well, but that just orders the results. Or using WHERE, but that only returns the lodges that have had comments. I thought HAVING might be it, but that just filtered the results again.
What I am looking for is to use GROUP BY, but for the summarised lodges (rather than all results) to be order by Comment so has if the lodge has any comments, I can use that to show or hide the link to the comments for that lodge.
I hope that makes sense, and might be possible.
EDIT - I found this on Stack Overflow which is what I am trying to do, but suggests its not really possible?