Get single rows of data

[nowandever29]

I need help with getting just one row of a subtable for each entry in the main table. For example, let's say I have:

Applications
============
AppID AppName
1 Open Office
2 Paint.Net
3 Firefox

Contacts
========
ConID AppID ContactName
1 1 Bill Forbes
2 1 Frank Jones
3 1 Bob Smith

When I do an outer join on these, I get three rows for App #1, as expected, each with a different contact name. But now I have the need to create a view that shows just one row per application, and includes (any one) of the contacts for that application.

I've yet to find how to do this - in Access, you can use FIRST(), but T-SQL (2008) doesn't have that. Can anyone help? I tried the blog at

http://blogs.lessthandot.com/index....ver/including-an-aggregated-column-s-related,

but it was related to numerical values, and did not help.

 

[RiverGuy]

SELECT x.AppID, x.AppName, c.ContactName
FROM
	(SELECT a.*, b.MinID
	FROM Appliations a
	LEFT OUTER JOIN
	  (SELECT AppID, MIN(ConID) AS MinID
	   FROM Contacts
	   GROUP BY AppID) b
	ON a.AppID = b.AppID) x
LEFT OUTER JOIN Contacts c
	ON x.AppID = c.AppID
	AND x.MinID = c.ConID

 

[markros]

Please see this link
Including an Aggregated Column's Related Values