Column contains more than one record: sql server 2005 2

[macsql1]

Hi,

I have three columns. however i would like to list common records.
i.e.111 and 124 which appears in multiple time.

How to figure out the name/id which is > 1. Using sql server 2005.


Table : Street

Name ID Street Name
--------------------------------
Lina 111 Randolp
Rosy 123 Lake
BOb 124 E Randolph
BOb 124 W Lake
Lina 111 E Lake
Peter 125 W Clark
Ryan 116 State st
BOb 124 Stanger st



Output should like :- ( Which contains more than one record)
Name ID
----------------
Lina 111
BOb 124


Thanks in advance.
-mac

 

[gmmastros]

Select   Name, Id
From     Street
Group By Name,Id
Having   Count(*) > 1

Let me know if you have any questions.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[macsql1]

Thanks George,

I got expected result:
Name ID
----------------
Lina 111
BOb 124

I have an another table where i have to map the id, pull multiple records from "StreetCentral" table

Here Oprid is primarykey in StreetCentral table
and Id is FK in Street table
----------
select name, * from StreetCentral where oprid=
(Select Id
From Street
Group By Id
Having Count(*) > 1)
-------------

Here it gives error:-
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


Appreciate your help

-mac

 

[RyanEK]

Hi,

If you want to return fields for the subquery you need to use a join. Try...

select t2.Name, t1.*
from StreetCentral t1
join (
  select   Name, Id
  From     Street
  Group By Name, Id
  Having   Count(*) > 1
) t2 on t2.Id = t1.oprid

Ryan