[ RESOLVED ] WHERE IN (Count only once)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

[ RESOLVED ] WHERE IN (Count only once)

Author

Topic

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2007-12-10 : 12:16:26

I am selecting from two tables, with a one-to-many relationship.

Select t2.*
From Table1 t1
inner join Table2 on t1.id = t2.id
where t2.Field2 in (1,3)

There are times when there are 2 or more entries in table2 that relate to table1. I want to only display 1 of these entries, (either will do), I just dont want to display all.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-10 : 12:30:24

select top 1 t2.* ...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2007-12-10 : 12:51:09

Thanks, your reply got me thinking.

LEFT OUTER JOIN MembCorp MC
ON MC.MembID = MP.MemberID
AND MC.MembCorpType = ( SELECT TOP 1 MembCorpType FROM MembCorp WHERE MembCorp.MembID = MP.MemberID AND MembCorpType IN (1,3)ORDER BY MembCorp.MembCorpType ASC )

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-10 : 13:48:24

Basically you wanted TOP 1 within group?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources