CODEWITHSUNDEEP
sqlmysqlgroup-by
Rob
Rob

Reputation: 8187

Limit results from joined table to one row

Here is a simplified table structure:

TABLE products (
 product_id INT (primary key, auto_increment),
 category_id INT,
 product_title VARCHAR,
 etc
);

TABLE product_photos (
 product_photo_id (primary key, auto_increment),
 product_id INT,
 photo_href VARCHAR,
 photo_order INT
);

A product can have multiple photos, the first product photo for each product (based on the photo_order) is the default photo.

Now, I only need all of the photos on the product details page, but on pages where I am listing multiple products, for example a product directory page, I only want to display the default photo.

So what I am trying to do, is query a list of products including the default photo for each product.

This obviously doesn't work, it will return all photos with the product info duplicated for each photo:

SELECT p.*, ph.*
FROM products AS p
LEFT JOIN product_photos AS ph
ON p.product_id=ph.product_id
ORDER BY p.product_title ASC

I need to figure out how to do something like this, but I don't know the syntax (or if it is possible)

SELECT p.*, ph.*
FROM products AS p
LEFT JOIN product_photos AS ph
    ON p.product_id=ph.product_id  **ORDER BY ph.photo_order ASC LIMIT 1**
ORDER BY p.product_title ASC

Edit: I figured out a solution with help from the answers below, thanks all!

SELECT p.*, ph.*
FROM products AS p
LEFT JOIN product_photos AS ph 
    ON p.product_id=ph.product_id
    AND ph.photo_order =
    (
        SELECT MIN(z.photo_order)
        FROM product_photos AS z
        WHERE z.product_id=p.product_id
    )
GROUP BY p.product_id
ORDER BY p.product_title ASC

Upvotes: 28

Views: 38023

Answers (4)

Wrikken
Wrikken

Reputation: 70460

SELECT p.*, ph.*
FROM products AS p
INNER JOIN product_photos AS ph
    ON p.product_id = ph.product_id
LEFT JOIN product_photos AS ph2
    ON p.product_id = ph2.product_id
    AND ph2.photo_order < ph.photo_order
WHERE ph2.photo_order IS NULL
ORDER BY p.product_title ASC

Note the how it joins to the product_photos table twice. The WHERE ph2.photo_order IS NULL will throw out all but the lowest photo order. It won't protect you against duplicate product_id / photo_orders combo though, you could add a GROUP BY on p.id if that's the case.

Upvotes: 20

Kovge
Kovge

Reputation: 2019

    SELECT p.*, ph.*
    FROM products AS p
    LEFT JOIN product_photos AS ph ON p.product_id=ph.product_id
    ORDER BY p.product_title ASC, ph.photo_order ASC
    GROUP BY p.product_id
    LIMIT 0,10

Upvotes: 3

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 798566

SELECT ...
  ....
GROUP BY p.product_id

Upvotes: 2

OMG Ponies
OMG Ponies

Reputation: 332551

Use:

SELECT p.*,
       pp.*
  FROM PRODUCTS p
  JOIN PRODUCT_PHOTOS pp ON pp.product_id = p.product_id
  JOIN (SELECT x.product_id,
               MIN(x.photo_order) AS default_photo
          FROM PRODUCT_PHOTOS x
      GROUP BY x.product_id) y ON y.product_id = pp.product_id
                              AND y.default_photo  = pp.photo_order

Upvotes: 14

Related Questions