Geek asks and answers

Choose the first related value, not all

I have tables:

'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'

I need to select a gallery (with some information about this, but it doesn’t matter) and only ONE photo identifier from the "Photos" table.

This (for big surprise) returns all the photos. (Duplicated Gallery Information)

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

EDIT

This returns only one gallery, not so many. I need to get one photo in one gallery ...

SELECT 
    photo_gallery.id_photo as id_photo
FROM
    gallery
        JOIN
    photo_gallery ON gallery.id_gallery = photo_gallery.id_gallery LIMIT 1
+3
source share
3 answers

Try the following query:

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
+1
source

use LIMIT

SELECT 
    pg.id_photo
FROM gallery g
LEFT OUTER JOIN (
    SELECT id_gallery, MAX(id_photo) AS id_photo photo_gallery pg
    GROUP BY id_gallery
) AS pg
    ON g.id_gallery = pg.id_gallery
0
source

try it

SELECT
gallery.name,
photo.photo_name
FROM
  gallery
  INNER JOIN photo_gallery
    ON (gallery.id_gallery = photo_gallery.id_gallery)
  INNER JOIN photo
    ON (photo.id_photo = photo_gallery.id_photo_gallery)
ORDER BY gallery.id_gallery DESC

if you want more data to write field names ...

0
source

More articles:


All Articles