Retrieving a list of rows with only a single item from other table in Oracle

Question

I have two tables in Oracle database (10g express)

product
product_image

One product can have multiple images. Hence, there a one-to-many relationship from product to product_image and the product_image table has a foreign key that refers to the primary key of the product table.

I need to fetch a list of products with only a single image name in each row of the result set being retrieved regardless of the images being in the product_image table (even though there are no images for some of products).

The image name to be retrieved from the product_image table is generally the first image name in the product_image table after sorting each set of images for each product in ascending order. Something like the following.

prod_id    prod_name    prod_image

      1    aaa          aaa.jpg      //The first image name in the product_image table after sorting images for prod_id in ascending order.
      2    bbb          bbb.jpg      //Similar to the first case.
      3    ccc            -          //No image(s) found in the product_image table 
      4    ddd            -          //Similar to the previous case.

The general join statement for these two tables would be something similar to the following.

SELECT p.prod_id, p.prod_name, pi.prod_image 
FROM product p 
INNER JOIN product_image pi 
ON p.prod_id=pi.prod_id;

Is this possible using a single SQL statement?

rs. · Answer 1 · 2013-01-22T15:42:54.830

1

Try this, you can generate row number for each image per prod_id and use that. To return null or blank from missing product image you should use left outer join

WITH CTE AS
(

SELECT prod_id, DBMS_LOB.substr(prod_image,1,4000) prod_image, 
row_number() over (partition by prod_id order by prod_image) rn
FROM product_image
)

SELECT p.prod_id, p.prod_name, nvl(pi.prod_image,'-') prod_image
FROM product p 
LEFT OUTER JOIN CTE pi 
ON p.prod_id=pi.prod_id and pi.rn = 1;

edited Jan 22 '13 at 15:42

answered Jan 22 '13 at 14:53

rs.

26,707
12
68
90

Oracle responds - `ORA-32033: unsupported column aliasing`. The column and table names in the database are similar to those you used in your answer. – Tiny Jan 22 '13 at 15:10
@Tiny, updated my answer, i mistyped `AS` keyword after `CTE` – rs. Jan 22 '13 at 15:43
I have accepted my own answer just because it is general SQL. – Tiny Jun 30 '14 at 15:20

Miguel Matos · Answer 2 · 2013-01-22T15:23:52.590

1

If I understood your question correctly I think the following query will work. I have not tested it.

SELECT p.prod_id, p.prod_name, MIN(DBMS_LOB.substr(pi.prod_image, 1))
FROM product p LEFT JOIN product_image pi 
ON p.prod_id=pi.prod_id 
GROUP BY p.prod_id, p.prod_name
ORDER BY p.prod_name;

edited Jan 22 '13 at 15:23

answered Jan 22 '13 at 14:57

Miguel Matos

191
7

Oracle responds - `ORA-00932: inconsistent datatypes: expected - got CLOB`. `pi.prod_image` is a `clob` type field in the database. – Tiny Jan 22 '13 at 15:12
Replace the MIN(pi.prod_image) by the MIN(image name field) – Miguel Matos Jan 22 '13 at 15:15
The *image name field* is `prod_image` (in the database). Hence, it is the same as your answer `MIN(pi.prod_image)`. – Tiny Jan 22 '13 at 15:18
I've edited my answer. Is there any reason why your are using a CLOB field to hold a file name instead of varchar2? – Miguel Matos Jan 22 '13 at 15:25

score 0 · Accepted Answer · edited May 23 '17 at 10:25

The following SQL worked as mentioned in the question.

SELECT 
     p.prod_id,
     p.prod_name,
     t.prod_image 
FROM
     product p 
LEFT OUTER JOIN(
     SELECT 
           pi.prod_image_id,
           pi.prod_id,
           pi.prod_image 
     FROM
           product_image pi 
     INNER JOIN (
           SELECT 
                MIN(pi.prod_image_id) AS prod_image_id 
           FROM 
                product_image pi
           GROUP BY 
                pi.prod_id
     ) prod_image 
     ON pi.prod_image_id=prod_image.prod_image_id
)t ON p.prod_id=t.prod_id ORDER BY p.prod_id DESC;

Reference:

SQL Select only rows with Max Value on a Column

Retrieving a list of rows with only a single item from other table in Oracle

3 Answers3