Получить товары только с фото

Вариант 1:

SELECT DISTINCT *
FROM tm_product p
INNER JOIN tm_photo ph ON p.id = ph.product_id
WHERE p.id_subcategory = $id_subcategory
ORDER BY p.id DESC

Вариант 2:

SELECT *
FROM tm_product p
WHERE p.id_subcategory = $id_subcategory
AND EXISTS ( SELECT 1
             FROM tm_photo ph 
             WHERE p.id = ph.product_id
           )
ORDER BY p.id DESC

Возможны и иные варианты.

Как-то так:

SELECT
  *
FROM
  tm_product AS p
LEFT JOIN
  tm_photos AS h
ON
  h.id_product = p.id
WHERE
  p.id_subcategory = $id_subcategory
ORDER BY
  p.id DESC