mysql - 选择第一个加入的值，而不是全部

Question

我有表:

'gallery' - information about existing galleries 
id_gallery  name     date
==========  ====  ===========
    1        ...   timestamp

'photo' - information and name of every photo in system
id_photo   photo_name
========   ===========
   1        some name 

'photo_gallery' - "connecting" table, which says which photo are in which gallery
id_photo_gallery   id_photo           id_gallery
================   ================   =================
       1           id from 'photo'    id from 'gallery'

我需要从“照片”表中选择图库(带有一些关于它的信息，但这并不重要)并且只有一个照片 ID。

这(令人大吃一惊)返回所有照片。 (有关画廊的重复信息)

SELECT 
    photo_gallery.id_photo as id_photo
FROM
    gallery
        JOIN
    photo_gallery ON gallery.id_gallery = photo_gallery.id_gallery

编辑

这只返回一个画廊，不多。我需要为每个画廊检索一张照片 ...

SELECT 
    photo_gallery.id_photo as id_photo
FROM
    gallery
        JOIN
    photo_gallery ON gallery.id_gallery = photo_gallery.id_gallery LIMIT 1

Answer 1

请尝试以下查询:

SELECT galery.*, photo.* 
FROM galery
LEFT JOIN photo_gallery ON galery.id = photo_gallery.id_gallery
LEFT JOIN photo ON photo_gallery.id_photo = photo.id
GROUP BY galery.id