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

user_824344 0
Viewed 18561

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.

row join mysql
3 Answers

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
);
user_57013360
Related