Duplicate area problem

[lfcmd]

Hi All

Quick Query, i have table that basically looks like

CName Id Area
Dunnes 10 11
Dunnes 10 12
Quinn 11 13
Quinn 11 14
Quinn 11 11
Tesco 12 12
Tesco 12 13

From the example above i want to find all CName whos name is 'dunnes' or 'quinn' and also have the same Area.

So result i would need from example above would be

Dunnes 11
Quinn 11

 

[Zathras]

Something like
[tt]
select cname,area
from thetable
where cname in ('dunnes','quinn')
group by cname,area
having count(*)>1
[/tt]

 

[lfcmd]

ye had tried somethin along those lines and just tried your example there now but getting no records returned when i run the query

 

[Zathras]

Is your installation configured for case-sensitive compares?

Is the "area" column text or integer?

Do any of the names have trailing spaces?

 

[lfcmd]

no not case - sesitive
"area" column is integer
no trailing spaces after the names

 

[Zathras]

Sorry, I misread the actual requirement. Try this:
[tt]
select cname,area
from thetable
where area in
(select area
from thetable
where cname in ('dunnes','quinn')
group by area
having count(*)>1)
[/tt]

 

[lfcmd]

cheers for that lovely stuff