How to Retrieve Only the First Row in a LEFT JOIN?

Retrieving Only the First Row in a LEFT JOIN

In SQL, performing a LEFT JOIN operation can result in multiple rows from the right table being matched to a single row from the left table. In some scenarios, it is desirable to retrieve only the first row from the right table for each row in the left table.

Consider the following simplified data structure:

**Feeds**
id | title | content
----------------------
1  | Feed 1 | ...

**Artists**
artist_id | artist_name
-----------------------
1 | Artist 1
2 | Artist 2

**feeds_artists**
rel_id | artist_id | feed_id
----------------------------
1 | 1 | 1
2 | 2 | 1

To retrieve the feed articles and associate only the first artist to each feed, the following syntax may be attempted:

SELECT *
FROM feeds
LEFT JOIN feeds_artists ON wp_feeds.id = (
    SELECT feeds_artists.feed_id FROM feeds_artists
    WHERE feeds_artists.feed_id = feeds.id
    LIMIT 1
)
WHERE feeds.id = '13815'

However, this approach may not yield the desired results. To achieve the goal, consider the following alternative:

SELECT *
FROM feeds f
LEFT JOIN artists a ON a.artist_id = (
    SELECT
      MIN(fa.artist_id) a_id
    FROM feeds_artists fa
    WHERE fa.feed_id = f.id
)

This modification uses the MIN() function to determine the artist with the lowest ID, assuming that artist IDs increment over time. As a result, the LEFT JOIN will only retrieve the first artist associated with each feed.

The above is the detailed content of How to Retrieve Only the First Row in a LEFT JOIN?. For more information, please follow other related articles on the PHP Chinese website!