Join two tables in MySQL, returning just one row from the second table

Question

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.

Steven Ryssaert · Accepted Answer · 2011-06-25T17:36:01.233

13

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'

edited Jun 25 '11 at 17:36

answered Jun 24 '11 at 13:24

Steven Ryssaert

1,989
15
25

I'm using mysql, I edited the post, my mistake for not saying that. – Raul Jun 24 '11 at 13:27
The principal is the same. Select your picture first row data first. You can accomplish this using `LIMIT 1`. Next use that Query in a `JOIN` as a subquery. – Steven Ryssaert Jun 24 '11 at 13:29
This would not work in MYSQL. You should better analyze this cause in the end instead of giving me multiple rows containing multiple rows from first table and only one from second table this returns just a single row, which to me makes sense cause the inner SELECT statement has one row. – Raul Jun 24 '11 at 14:25
The inner select statement returns just a single row (because of that LIMIT 1 not one row for each row from the first table. 1 ROW ALONE. So how do I go around that LIMIT 1 but still selecting 1 row for each row from the first table. – Raul Jun 24 '11 at 14:32
Actually it is far more simple than I first had imagined. I have played with it on a test database on my development machine and came up with the updated query above. – Steven Ryssaert Jun 25 '11 at 17:32
I came up with the solution of adding a GROUP BY also but I don't like what it shows when I type EXPLAIN in front of the query. It will work but as the number of galleries grows this is going to be very slow or at least so I think. Feel free to contradict me. – Raul Jun 26 '11 at 10:25
I added a new answer that might help you. Can you verify if it does what you need? – Steven Ryssaert Jun 28 '11 at 07:37

score 0 · Answer 2 · answered Jun 27 '11 at 08:58

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

mhsallam · Answer 3 · 2021-04-19T20:32:00.110

Solution 1 The method that I used is to add row number to the sub-set result (in our case pictures query) using ROW_NUMBER() function then in the join condition I added (rn = 1)... it would be something like the following:

SELECT g.* FROM gallery g 
LEFT JOIN (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn FROM pictures
) p ON g.id = p.gallery_id AND p.rn = 1;

Edit: I didn't notice this is for MySQL my answer was for PostgreSQL, but I believe the technique is still valid if you know how to add row numbers to the query.

Solution 2: This is another technique you could use, without the need to add row_number of grouping, which is basically by adding sub-query in the join condition to pick only one row from related pictures (I know I'm sill using PostgreSQL, but I believe it would still be applicable in MySQL, or just overlook that and use the technique only):

SELECT g.* FROM gallery g 
LEFT JOIN pictures p ON g.id = p.gallery_id AND p.id = (
    SELECT id FROM pictures WHERE gallery_id = g.id LIMIT 1
);

Join two tables in MySQL, returning just one row from the second table

3 Answers3

Linked