Help with select - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Help with select

Author

Topic

ljp099
Yak Posting Veteran

79 Posts

Posted - 2011-11-09 : 12:42:52

I have a table, Contracts, with the following data:

SfId | InternalId
abc123 1400
abc123 1400
abc123 1500
abc123 1500

The SfId should be unique for a given InternalId. I use this query to give me the distinct InternalIds.

Select distinct InternalId from Contracts

returns:

InternalId
1400
1500

Now what I need is a query to return the InternalId of records where the SfId is not unique grouped by InternalId (if the same SfId value exists in more than 1 distinct InternalId, then it should appear as a result in this query).

This table data:

SfId | InternalId
abc123 1400
abc123 1400
abc123 1500
abc123 1500

should return:

1400
1500

since both have the SfId value: abc123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-09 : 12:45:48

[code]
SELECT DISTINCT InternalId
FROM table t
INNER JOIN (SELECT SfId
FROM table
GROUP BY SfId
HAVING COUNT(DISTINCT InternalId)> 1)t1
ON t1.SfId = t.SfId
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/