Pulling records that only have different fields

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

Pulling records that only have different fields

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-08-22 : 11:06:07

I am not sure if this can be done or not. I am trying to billed some kind of query that would tell me how many doctors have to different pcs_id.

Example

pcs_id Full Name Int Specialty
00061280 Bruce Abbott E POD
00103421 Bruce Abbott POD

How can I pull the ones that have two different pcs_id and not the one that just have one pcs_id. I know this might sound confusing.

This is what I wrote.

select
pcs_id1,PCS_FNAME + ' ' + PCS_LNAME as 'FullName',PCS_MINIT,PCS_DEGREE,PCS_SPEC1, count(PCS_FNAME + ' ' + PCS_LNAME)
from PCS
Where
pcs_degree <> 'HOSP' and
pcs_degree <> 'ANC' and

Group by PCS_FNAME + ' ' + PCS_LNAME,pcs_id1,PCS_MINIT,PCS_DEGREE,PCS_SPEC1
order by fullname

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-22 : 11:09:41

is this what you want ?


select PCS_FNAME
from   PCS
group by PCS_FNAME
having count(distinct pcs_id) > 1

KH
[spoiler]Time is always against us[/spoiler]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-22 : 11:10:32

[code]
SELECT
[PCS_FNAME] + ' ' + [PCS_LNAME]
FROM
PCS
GROUP BY
[PCS_FNAME] + ' ' + [PCS_LNAME]
HAVING
COUNT([pcs_Id]) > 1
[/code]

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-22 : 11:14:17

You probably do need the DISTINCT as posted by khtan.

-------------
Charlie

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-08-22 : 11:34:19

This is great! Thank you. Could I also add the PCS_SPEC1 field. The reason why I wanted to do that is because there might be a doctor that has the same name, but different specialty. Is that possible?

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-08-22 : 11:46:08

can I also had the pcs_id back into the select statment so I can see the number?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-22 : 13:25:26

quote:
Originally posted by werhardt

can I also had the pcs_id back into the select statment so I can see the number?

just use the query provided inside a subquery and select like below

SELECT *
FROM  PCS
WHERE PCS_FNAME IN 
(select PCS_FNAME
from   PCS
group by PCS_FNAME
having count(distinct pcs_id) > 1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources