CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
justacoder
justacoder

Reputation: 2704

Retrieving distinct record from inner join

I need to retrieve the first image from portfolio_images, and my following query isn't doing that. It's returning all images for each portfolio record found (this works with DISTINCT), when I only need the first portfolio_images.fullsize record it finds.

portfolio

id | project_name | location_id | project_type | is_active

portfolio_images

id | portfolio_id | fullsize

 SELECT DISTINCT p.project_name, pi.fullsize
    FROM portfolio p
    INNER JOIN portfolio_images pi ON p.id = pi.portfolio_id
    AND p.is_active = 1
    AND p.project_type = 'project' AND p.location_id = 3
    ORDER BY p.oindex

I want to avoid having to query portfolio first, then looping through those results and doing a nested loop to query portfolio_images for the first returned record based on the current portfolio ID.

Upvotes: 1

Views: 78

Answers (1)

John Woo
John Woo

Reputation: 263723

add a subquery that gets the last ID for each portfolio_id on table portfolio_images. this will assure that you will get one record for each project_name.

SELECT  p.project_name, pi.fullsize
FROM    portfolio p
        INNER JOIN portfolio_images pi 
            ON p.id = pi.portfolio_id AND 
                p.is_active = 1 AND 
                p.project_type = 'project' AND 
                p.location_id = 3
        INNER JOIN
        (
            SELECT  portfolio_id, MAX(ID) max_ID
            FROM    portfolio_images 
            GROUP BY portfolio_id
        ) a ON pi.portfolio_id = a.portfolio_id AND
                pi.ID = a.max_ID
ORDER   BY p.oindex

Upvotes: 2

Related Questions