August 22, 2006 at 12:14 pm
I want to run a join select query on two tables/views, but one of the tables has multiple rows for each row in the first, but I don't care about that, and I only want one row for each row in the first view in the result set.
Here's a simple example:
tblCompanies (companyId companyName)
1, Worldcom Communications
2, Tyco, Inc.
3, Enron Energy
tblContacts (contactId, companyId, contactName)
1, 1, Billy Jones
2, 1, Fred Johnson
2, 2, Bob Zimmerman
2, 2, Carla Franks
2, 2, Samir Ahmadinajad
3, 3, Larry Stephens
So there are multiple contacts for each company, but I just want one contact only to show up in the join select and I don't care which. I know the first thing you might think to yourself is, "why the hell would you ever want to do that?" Basically I inherited a poorly designed database and now I work with it while I am fixing it...nuff said about that.
I appreciate any help, thanks!
August 22, 2006 at 12:26 pm
Try something like:
select *
from dbo.Companies C
join dbo.Contacts E on C.companyId = E.companyId
where E.contactId = ( select min(E1.contactId)
from dbo.Contacts E1
where E1.companyID = C.companyID)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply