Retreive one row from 1 to many relationship between three tables

Question

I have the following three tables :

----PRODUCT----

PRODUCT_ID     DESC     
1              'Pencil'   
2              'Paper'

----PRICE_BY_SUPPLIER----

PRODUCT_ID     SUPPLIER_ID     PRICE  
1              1               10  
1              2               9  
1              3               9.5  
2              1               5

----IMAGES_BY_PRODUCT----

PRODUCT_ID     NAME  
1              'pencil.img'  
1              'pen.img'  
1              'pencil_other.img'  
2              'paper.img'

I would like a query that pull the minimum price, the count of supplier that hold the product and one image (one image among all suppliers). The output query should look like this:

----FINAL_QUERY----

PRODUCT_ID     MIN_PRICE     IMAGE          SUPPLIER_COUNT  
1              9             'pencil.img'   3  
2              5             'paper.img'    1

I have this query that return everything except the image.

SELECT f.PRODUCT_ID, f.DESC, x.MIN_PRICE, x.SUPPLIER_COUNT  
  FROM (
        SELECT pp.PRODUCT_ID, 
               MIN(pp.PRICE) AS MIN_PRICE, 
               COUNT(pp.PRODUCT_ID) AS SUPPLIER_COUNT 
          FROM PRICE_BY_SUPPLIER AS pp 
         GROUP 
            BY pp.PRODUCT_ID
       )   
       AS x
       INNER JOIN PRODUCT AS f 
         ON f.PRODUCT_ID = X.PRODUCT_ID

Can you help me complete my query ?

Answer 1

You could add another join to fetch an image. For example, the max image per productid:

SELECT  f.PRODUCT_ID, f.DESC, x.MIN_PRICE, x.SUPPLIER_COUNT, img.Name
FROM    ( 
        select  pp.PRODUCT_ID
        ,       MIN(pp.PRICE) as MIN_PRICE
        ,       COUNT(pp.PRODUCT_ID) as SUPPLIER_COUNT 
        from    PRICE_BY_SUPPLIER as pp 
        group by 
                pp.PRODUCT_ID
        ) AS x 
JOIN    PRODUCT as f 
on      f.PRODUCT_ID = X.PRODUCT_ID
JOIN    (
        select  max(name)
        from    images_by_product
        group by
                product_id
        ) as img
on      img.PRODUCT_ID = X.PRODUCT_ID

If the image is optional, replace the join with an outer join. Depending on your DBMS, you can also fetch a random image. For example, in SQL Server, you could replace the join with cross apply:

...
on      f.PRODUCT_ID = X.PRODUCT_ID
CROSS APPLY
        (
        select  top 1 name
        from    images_by_product
        where   PRODUCT_ID = X.PRODUCT_ID
        order by
                newid()
        ) as img

Or even directly in the select clause:

SELECT  f.PRODUCT_ID, f.DESC, x.MIN_PRICE, x.SUPPLIER_COUNT, img.Name
,       (
        select  top 1 name
        from    images_by_product
        where   PRODUCT_ID = X.PRODUCT_ID
        order by
                newid()
        ) as img
FROM    ( 
        select  pp.PRODUCT_ID
...

Answer 2

Try this:

SELECT DISTINCT p.product_id, MIN(pbs.price), ibp.name, pbs.supplier_id
FROM product p
INNER JOIN price_by_supplier pbs
ON  p.product_id = pbs.product_id
INNER JOIN images_by_product ibp
ON p.product_id = ibp.product_id
GROUP BY p.product_id

I was able to get your exact output (other than the supplier_count as that didn't make sense to me. Please explain further if you could.

PRODUCT_ID     MIN_PRICE     IMAGE          SUPPLIER_ID  
1              9             'pencil.img'   1  
2              5             'paper.img'    1

UPDATED:

This is kind of a lame way to get what you want but... It does work:

SELECT DISTINCT p.product_id, MIN(pbs.price), ibp.name, COUNT(pbs.supplier_id)
FROM product p
INNER JOIN images_by_product ibp
ON p.product_id = ibp.product_id
INNER JOIN price_by_supplier pbs
ON  p.product_id = pbs.product_id
GROUP BY  pbs.price
LIMIT 2


PRODUCT_ID     MIN_PRICE     IMAGE          SUPPLIER_ID  
2              5             'paper.img'     1  
1              9             'pencil.img'    3

Answer 3

Rather than get involved with all those horrific joins, couldn't you try something like...

SELECT PRODUCT_ID,
       (SELECT MIN(PRICE)
             FROM PRICE_BY_SUPPLIER
             WHERE PRICE_BY_SUPPLIER.PRODUCT_ID = PRODUCT.PRODUCT_ID) AS MIN_PRICE,
       (SELECT NAME
             FROM PRODUCT_IMAGES
             WHERE PRODUCT_IMAGES.PRODUCT_ID = PRODUCT.PRODUCT_ID
             LIMIT 1) AS PRODUCT_IMAGES,
       (SELECT COUNT(*)
             FROM PRICE_BY_SUPPLIER
             WHERE PRICE_BY_SUPPLIER.PRODUCT_ID = PRODUCT.PRODUCT_ID) AS SUPPLIER_COUNT
    FROM PRODUCT_ID;