I have a table containing metadata of photos: which gallery photo belongs to, which person it belongs to (person could be identified either by name, or by internal id assigned inside each gallery), whether or not a person in a photo wears regular glasses or dark glasses, or neither:
+----+------------+----------------------+------------------+----------------+------------------+----------------+
| id | gallery_id | person_id_in_gallery | person_name_id | wear_glasses | wear_sunglasses | image_filename |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
| 1 | 1 | 1 | NULL | 1 | 0 | xa.jpg |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
| 2 | 1 | 1 | NULL | 1 | 0 | xb.jpg |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
| 3 | 1 | 3 | NULL | 1 | 0 | xc.jpg |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
| 4 | 2 | 1 | NULL | 0 | 1 | ya.jpg |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
| 5 | 2 | 2 | NULL | 0 | 1 | yb.jpg |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
| 6 | 2 | 2 | NULL | 0 | 1 | yc.jpg |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
| 7 | 2 | 3 | NULL | 0 | 1 | yd.jpg |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
| 8 | 3 | NULL | 1 | 1 | 0 | za.jpg |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
| 9 | 3 | NULL | 1 | 1 | 0 | zb.jpg |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
| 10 | 3 | NULL | 2 | 0 | 1 | zc.jpg |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
| 11 | 3 | NULL | 2 | 0 | 1 | zd.jpg |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
| 12 | 3 | NULL | 3 | 0 | 0 | ze.jpg |
+----+------------+----------------------+------------------+----------------+------------------+----------------+
Now, my task is to obtain a list of photos that contain a person wearing either reg. glasses or dark glasses, but I need to only find one such photo of EACH of the people in the whole table. It gets slightly complicated, because a person could be identified either by global id throughout all databases (person_name_id), or by a local id in each gallery (person_id_in_gallery). The desired query result is as follows:
+----------------+
| image_filename |
+----------------+
| xa.jpg |
+----------------+
| xc.jpg |
+----------------+
| ya.jpg |
+----------------+
| yb.jpg |
+----------------+
| yd.jpg |
+----------------+
| za.jpg |
+----------------+
| zc.jpg |
+----------------+
Here is a query I used to obtain all the photos with glasses and sunglasses.
SELECT image_filename FROM photos_table WHERE (wear_glasses = 1 or wear_sunglasses = 1) and (person_id_in_gallery IS NOT NULL or person_name_id IS NOT NULL)
However, all my attempts to use DISTINCT and GROUP BY statements to pick a single photo from each unique person failed, and I would greatly appreciate help with using them properly in this scenario.
You can try the following (tested on dbfiddle)
SELECT MIN(image_filename) AS image_filename
FROM photos_table
WHERE (wear_glasses = 1 or wear_sunglasses = 1)
AND (person_id_in_gallery IS NOT NULL or person_name_id IS NOT NULL)
GROUP BY gallery_id, person_id_in_gallery, person_name_id;