Query to get distinct fields
-
Hi all,
Can anyone help me with this query:
I need a list of the NInumbers that exist more than once in the table but in different departments, i.e. NInum 123 exists twice, but once in dept 1 and once in dept 3. How do I get that? So far I have this but it groups by NInumber so it doesn't show different departments.
Table Structure:
Ninumber
Surname
Department
Query:
SELECT COUNT(*) AS Expr1, NINumber, department
FROM staff
GROUP BY NINumber, department
HAVING (COUNT(*) > 1)
ORDER BY NINumber
Results:
Expr1
NINumber
department
2
JA046262C
6
2
JB120106C
6
2
JC930105B
6
2
JE930767D
6
2
JG668966C
9
2
JG950486C
6
2
JN715890C
0
2
JN916223D
6
2
JS698737B
6
3
NA210020C
6
The results above imply that there are 2 of each NInumber but all in department 6. I only want the list of NInumbers that occur in different departments.
can anyone help?
Thank you very much in advance.
Paula.
-
SELECT DISTINCT NInumber
WHERE NInumber IN
(
SELECT NINumber
FROM staff
GROUP BY NINumber, department
HAVING (COUNT(*) > 1)
)
-
Thanks for trying but this still brings back the same as mine roughly, i.e. all ninumbers that exist more than once, but not just the ones that exist within different departments. E.g, I get:
Ninumber 123 Dept 6
Ninumber 123 Dept 6
rather than......
Ninumber 123 Dept 3
Ninumber 123 Dept 6
I need a list where the departments are different, not the cases where a peson exists in the same department twice.
Thanks very much anyway! Keep trying folks, this is really winding me up now!!
-
Sorry Didn;t read right...
SELECT DISTINCT NInumber, department
FROM Staff
WHERE
NInumber IN
(
SELECT NINumber
FROM staff
GROUP BY NINumber
HAVING (COUNT(DISTINCT department) > 1)
)
-
Ah, hallelujah! That's it! A million thanks. I'd been trying all sorts of distinct / having count type combinations but just couldn't get it!
-
Here are a few other options. The middle one would be my favourite.
--data
declare @staff table (NINumber varchar(10), department int)
insert @staff
select 'JA046262C', 6
union all select 'JB120106C', 6
union all select 'JB120106C', 7
union all select 'JB120106C', 6
union all select 'JC930105B', 6
--calculation
select distinct NInumber, department
from @Staff a where exists (select * from @Staff where NInumber = a.NInumber and not department = a.department)
select NInumber, department, count(*) as count
from @Staff a where exists (select * from @Staff where NInumber = a.NInumber and not department = a.department)
group by NInumber, department
select distinct a.*
from @Staff a inner join @Staff b on a.NInumber = b.NInumber and not a.department = b.department
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
I tried all of these. The first two are great but the third brings back 4 extra rows! I'm a bit confused. Just crap data do you think? The where clause is basically the same in all three though.
-
It is possible that the surName has variations in spelling?
-
Ah, yes, because it's a.*. Yes that will be it. Thanks. It's been a long day, my brain has gone to sleep too early.
-
Happens to the best of us. That's why we come here. Take care!
-KM
-
Thanks to all.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply