Join two tables in MySQL, returning just one row f

问题:

I have two tables: gallery and pictures:

gallery

id           int (auto increment, primary key)
name         varchar

pictures

id           int (auto increment, primary key)
picture      varchar
gallery_id   int (foreign key)

How do I join these two tables showing for each row from the left table (gallery) just the first row from the second table, without going through all the rows from the second table? I am using MySQL.

My objective is to make a page containing a list of the existing galleries showing a picture for each gallery as a link to the details page with all the pictures of that gallery.

---

I have searched this question on this site but the similar questions are too complicated. I'm only interested in this simple example.

回答1:

EDITED

Apparently grouping in MySQL database would do the trick for you.

Database columns are main_id, sub_id, sub_main_id, sub_data

SELECT *
FROM tblmain
  inner join sub on sub.sub_main_id = main_id
group by main_id;

without the group i have these records:

1, 1, 1, 'test 1'
1, 2, 1, 'test 2'
2, 3, 2, 'test 3'
3, 4, 3, 'test 4'
2, 5, 2, 'test 5'

after grouping, I get this result:

1, 1, 1, 'test 1'
2, 3, 2, 'test 3'
3, 4, 3, 'test 4'

回答2:

Second option ( without grouping ) is by using internal row numbering, and limiting the row number to the first occurence.

set @gallery_id = '';
set @num  = 1;


SELECT *
FROM gallery
    INNER JOIN (
        select id, picture, gallery_id
        from (
            select
                id, 
                picture, 
                gallery_id,
                @num := if(@gallery_id = gallery_id, @num + 1, 1) as row_number,
                @gallery_id := gallery_id as dummy
            from pictures
        ) as pictureRows
        where pictureRows.row_number = 1
    ) as firstPicture ON firstPicture.gallery_id = gallery.id;

Hope this helps for you