Get only single row results per id?

Hi, is it possible to make an sql query that has an Outer Join but return only one row of results max per id.

For example i have an Articles table, and a PicturesForArticles table.

The Articles table has an id field(aid), a title field(aTitle) and a content field(aContent).

And the PicturesForArticles table has an id field(pid), a PicPath filed and a field linking it to the articles table(aid)

Obviously the PicturesForArticles field stores pictures for the articles, and article can have a multiple number of pictures, or no pictures at all.

So i want to make a query that will return all of the Articles fields and a picture for each article. Even if the article has many pictures i only want to get a single row for each aid(Articles Id), and if there are no pictures for that article the picture fields will be null.

Is there any way to do this, to only return on row of results for each aid?

Thanks

That's very similar to something that I discussed in one of my articles on SingingEels :http://www.singingeels.com/Articles/How_To_Maintain_Customer_Payment_History.aspx

Basically, you can use the "SubSelect" method I did under the "Joining The Tables Together" subheading.

Let me know if you need more help with this, if not, then please mark this post as the answer.

Thanks,

|||

Did you mean the first query after the subheading?

If so then how can i select more than one field from the derived table, do i need another derived table?

|||

At the end of the article I show how to get more than one field from a 'vertical' table, but grouped by a certain criteria (in my case "CustomerID")

SELECT
Customers.*,
LatestPayments.*
FROM
dbo.CustomersLEFTOUTERJOIN
(SELECT CustomerID,MAX(ID)AS LastPaymentID,MAX(PaymentDate)AS LastPaymentDate
FROM dbo.PaymentHistoryGROUPBY CustomerID) LatestPayments
ON Customers.ID = LatestPayments.CustomerID

That's the code fromhttp://www.singingeels.com/Articles/How_To_Maintain_Customer_Payment_History.aspx that shows how to do that... if you are having troubles modifying it to your needs, then please past your table definitions and I'll change it to work for you.

Thanks,

|||

Ok thanks