;with my_cte_media as 
 (select 
   product_id,
   media_name as prod_image,
   row_number() over (partition by product_id order by product_id,media_unique_id) as rownum
  from tbl_media)
SELECT  
 productid, 
 description, 
 rangename, 
 min(cte.prod_image) as prod_image
from products  
inner join range 
           on products.range = range.rangeid 
left outer join my_cte_media as cte 
           on products.productid = cte.product_id and cte.rownum = 1
where range=227 
group by products.productid, products.range, products.description, range.rangename

quote:

---

Originally posted by Peso

SELECT	Media_ID,
	Product_ID,
	Media_Ext,
	Media_Name
FROM	(
		SELECT	Media_ID,
			Product_ID,
			Media_Ext,
			Media_Name,
			ROW_NUMBER() OVER (ORDER BY Media_ID) AS recID
		FROM	tbl_Media
		WHERE	Product_ID = 575
	) AS d
WHERE	recID = 1

---

E 12°55'05.63"
N 56°04'39.26"

---

quote:

---

Originally posted by visakh16

SELECT p.Product_id,p.Description,r.RangeName,m.media_name
FROM tbl_Product p
inner join range r on p.range = r.rangeid 
INNER JOIN tbl_media m
ON m.Product_ID=p.Product_id
INNER JOIN (SELECT Product_id,MIN(Media_Id) AS First
            FROM tbl_Media
            GROUP BY Product_Id)m1
ON m1.Product_id=m.Product_id
AND m1.First=m.Media_Id

---

quote:

---

Originally posted by jcelko

"A problem well stated is a problem half solved." -- Charles F. Kettering

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

I am confused by the sentence "the unique ID in tbl_media" because there is no such thing as a magical universal identifier in RDBMS and unless this deals with furniture, the "tbl-" is both silly and a violation of ISO-11179 rules.


--CELKO--
Joe Celko, SQL Guru

---