Limit results from joined table to one row

Question

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

Pls provide an example of PRODUCT_PHOTOS - I want to see how PHOTO_ORDER controls the default because you didn't provide data types — OMG Ponies, Jun 05 '10 at 01:26
photo_order is just an integer, it *should* be unique per product but not necessarily guaranteed — Rob, Jun 05 '10 at 01:40
If you switch to a right join and order by: .product_title ASC, .photo_order ASC I think you will get one row per product and it will be the first photo. You will not see products that do not have a photo (which may be an issue). It should be a far simpler query though. — TheJacobTaylor, Jun 05 '10 at 01:43
Yes that would be a problem, as there is no guarantee that all products will have at least one photo (most likely many will not) — Rob, Jun 05 '10 at 01:45

score 20 · Answer 1 · edited Jan 08 '15 at 20:42

20

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.

edited Jan 08 '15 at 20:42

Patrick M

10,547
9
68
101

answered Jun 05 '10 at 01:29

Wrikken

69,272
8
97
136

3

Nice job. No sub query/derived table. Just need indexes on product_id and photo_order. – Marcus Adams Jun 05 '10 at 02:00

score 14 · Accepted Answer · answered Jun 05 '10 at 01:24

14

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

answered Jun 05 '10 at 01:24

OMG Ponies

325,700
82
523
502

1

i don't know that this does what he's asking. he was specific that the photo_order field be used, not the product_photo_id field. – nathan gonzalez Jun 05 '10 at 01:28
1

It does exactly what is needed, I just try to avoid subqueries when I can, hence the other answer. – Wrikken Jun 05 '10 at 01:31
1

@Wrikken: A join to a derived table/inline view isn't a subquery. To me, at least... – OMG Ponies Jun 05 '10 at 01:34
1

agreed on the derived table. did you change your anwser or did i completely misread it? apologies if i was in the wrong. – nathan gonzalez Jun 05 '10 at 01:40
3

ohoh, not getting into that. Not quite flame-war-bait, but whether a derived table is a subquery is very open for debate, which I will not start now. And it wasn't a criticism, my disuse of subqueries and / or derived tables sometimes borders the illogical. – Wrikken Jun 05 '10 at 03:34

score 3 · Answer 3 · answered Feb 23 '13 at 08:30

3

    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

answered Feb 23 '13 at 08:30

Kovge

2,019
1
14
13

score 2 · Answer 4 · answered Jun 05 '10 at 01:31

2

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

answered Jun 05 '10 at 01:31

Ignacio Vazquez-Abrams

776,304
153
1,341
1,358

1

Thanks for the reply, although this solution won't honor the photo_order column. – Rob Jun 05 '10 at 01:44
1

Try reversing the sort order. – Ignacio Vazquez-Abrams Jun 05 '10 at 01:51

Limit results from joined table to one row

4 Answers4

Linked