Stored Procedure - Select all rows from one table and first row from another

I wish to select all post from Table Category and the first row in Product table.

SQL goes (not working just to show you how im thinking):

SELECT Category.CategoryID, Category.Title, TOP (1) Product.ImagePath, TOP (1)  Product.CategoryID

FROM Category INNER JOIN

Product ON Category.CategoryID = Product.CategoryID GROUP BY Category.CategoryID, Category.Title

Thanks for your help!

0
flea8
9/16/2008 7:55:45 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

5 Replies
709 Views

Similar Articles

[PageSpeed] 51

 

SELECT Category.CategoryID, Category.Title, Product.ImagePath,   Product.CategoryID

FROM Category crossJOIN

(select top 1 * from  Product ) P


Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
0
ramireddyindia
9/16/2008 10:50:54 AM

It works almost perfectly...

But the thing is I want to get the top 1 Imagepath from table Products for each different category. Now it returns top 1 from products to all categories

SELECT Category.CategoryID,Category.Title,Product.ImagePath,Product.CategoryID

FROM Category CROSS JOIN

(SELECT TOP 1 * FROM Product )

Product

0
flea8
9/16/2008 11:29:49 AM

?

0
flea8
9/16/2008 11:32:33 AM

 SELECT c.CategoryID,c.Title,t1.ImagePath,t1.CategoryID
FROM Category c inner JOIN
(select categoryid,imagepath from  (SELECT productid,categoryid,imagepath,row_number() over (partition by categoryid order by productid) as rn FROM Product ) t where rn = 1) t1
on c.categoryid = t1.categoryid


Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
0
ramireddyindia
9/16/2008 11:41:53 AM

Thank you man! Works great!

0
flea8
9/16/2008 11:45:05 AM
Reply: