SQL ignore dupe records

Hi,

I'm writing a query that will return people based on the office they exist in. The data below is a made sample.
I need the result set of a query for everyone in office 'nyc', except for 'mike smith' because he is already in the office 'la'. I only want people returned for a given office if they don't already exist in another office.

So based on the data below, for the result set I would like
john doe and john lennon.

It seems like it should be simple but I'm having a hard time.
Thanks!
Nacht


Table: employees

fname: mike
lname: smith
office: nyc

fname: mike
lname: smith
office: la

fname: john
lname: doe
office: nyc

fname: john
lname: lennon
office: nyc
LVL 1
nachtmskAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
try this query
select fname,lname
  from employees
 group by fname,lname
having count(distinct office) = 1
  and max(office) = 'nyc'
  and min(office) = 'nyc'

Open in new window

http://sqlfiddle.com/#!3/4216a/1
0
 
aboo_sCommented:
SELECT fname,lname FROM employees WHERE office = 'nyc' AND COUNT(SELECT * FROM employees WHERE fname = fname AND lname=lname AND office = 'la') = 0 ;
0
 
lcohanDatabase AnalystCommented:
select fname,lname,office from employees
group by office
having COUNT(distinct fname) = 1
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
aboo_sCommented:
or SELECT fname as fname1,lname as lname1 FROM employees WHERE office = 'nyc' AND COUNT(SELECT * FROM employees WHERE fname = fname1 AND lname=lname1 AND office = 'la') = 0 ;
0
 
lcohanDatabase AnalystCommented:
OR:

select fname,lname,office from employees
group by office
having COUNT(fname+lname) = 1
0
 
nachtmskAuthor Commented:
Icohan,
What your suggesting seems exactly what I need.
However, when I'm running it I'm getting a syntax error. I'm using SQL Server 2005

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ')'.


Ideas?
Nacht
0
 
nachtmskAuthor Commented:
I tried this one: select fname,lname,office from employeeList
group by office
having COUNT(fname+lname) = 1

and got this error message
Msg 8120, Level 16, State 1, Line 1
Column 'employeeList.fname' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
 
lcohanDatabase AnalystCommented:
Sorry I added those to the count and miss them from group by...this should work:

select fname,lname,office from employeeList
group by office,fname,lname
having COUNT(fname+lname) = 1

For "proof" you could add the "count" column and try the reverse like

select fname,lname,office,COUNT(fname+lname) as numb from employeeList
group by office,fname,lname
having COUNT(fname+lname) > 1
0
 
nachtmskAuthor Commented:
Thanks.
that worked, but it returns too much. I don't want EITHER record returned if there is a dupe of it. The query you gave me returns once instance of the duped record. I want everyone except all instances of the dupe record. Possible?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.