mysql - 如何从连接的 SQL 表创建报告？

Question

我有三个表:

 pindex_photos:        pindex_names:    pindex_link:
 | id  | filename  |   | id | name  |   | photo_id | name_id |
 |-----|-----------|   |----|-------|   |----------|---------|
 | 1   | DSC1.jpg  |   | 1  | Leo   |   | 1        | 3       |
 | 2   | DSC2.jpg  |   | 2  | Liz   |   | 1        | 2       |
 | 3   | DSC5.jpg  |   | 3  | Tom   |   | 3        | 1       |
 | ..  | ..        |   | .. | ..    |   | 2        | 1       |
                                        | ..       | ..      |

在 pindex_link 中，前两个表与其 ID 相关联。现在我想从此表制作一个报告文件，如下所示:

 Report on Images (list only Images that have > 0 connections to names)
 DSC1.jpg (2): Liz, Tom
 DSC2.jpg (1): Leo
 ..

 Report on Names (list only Names that have > 0 connections to images)
 Leo (2): DSC2.jpg, DSC5.jpg
 Liz (1): DSC1.jpg
 ..

有人可以给我帮助或提供如何实现这一目标的线索吗？

Answer 1

select p.filename, group_concat(n.name)
from pindex_photos p
left join pindex_link l on l.photo_id = p.id
left join pindex_names n on l.name_id = n.id
group by p.filename

和

select n.name, group_concat(p.filename)
from pindex_photos p
left join pindex_link l on l.photo_id = p.id
left join pindex_names n on l.name_id = n.id
group by n.name